I’ve written a couple of articles in the past about the problems of ASSM spending a lot of time trying to find blocks with usable free space. Without doing a bit of rocket science with some x$ objects, or O/S tracing for the relevant calls, or enabling a couple of nasty events, it’s not easy proving that ASSM might be a significant factor in a performance problem – until you get to 12c Release 2 where a staggering number of related statistics appear in v$sysstat.
I’ve published the full list of statistics (without explanation) at the end of this note, but here’s just a short extract showing the changes in my session’s ASSM stats due to a little PL/SQL loop inserting 10,000 rows, one row at a time into an empty table with a single index:
Name Value ---- ----- ASSM gsp:get free block 185 ASSM cbk:blocks examined 185 ASSM gsp:L1 bitmaps examined 187 ASSM gsp:L2 bitmaps examined 2 ASSM gsp:Search hint 2 ASSM gsp:good hint 185
It looks like we’ve checked a couple of “level 2” bitmap blocks (one for the table, one for the index, presumably) to pick a sequence of “level 1” bitmap blocks that have been very good at taking us to a suitable data (table or index) block that can be used.
You might have expected to see numbers more like 10,000 in the output, but remember that PL/SQL has lots of little optimisations built into it and one of those is that it pins a few blocks while the anonymous block is running so it doesn’t have to keep finding blocks for every single row.
In comparison here’s the effect of the same data load when operated as 10,000 separate insert statements called from SQL*Plus:
Name Value ---- ----- ASSM gsp:get free block 10,019 ASSM cbk:blocks examined 10,220 ASSM cbk:blocks marked full 201 ASSM gsp:L1 bitmaps examined 10,029 ASSM gsp:L2 bitmaps examined 6 ASSM gsp:L2 bitmap full 1 ASSM gsp:Search all 1 ASSM gsp:Search hint 2 ASSM gsp:Search steal 1 ASSM gsp:bump HWM 1 ASSM gsp:good hint 10,016 ASSM rsv:fill reserve 1
It’s interesting to note that in this case we see (I assume) a few cases where we’ve done the check for an L1 bitmap block, gone to a data blocks that was apparently free, and discovered that our insert would make to over full – hence the 201 “blocks marked full”.
Critically, of course, this is just another of the many little indications of how “client/server” chatter introduces lots of little bits of extra work when compared to the “Thick DB “ approach.
One final set of figures. Going back to an example that first alerted me to the type of performance catastrophes that ASSM could contribute to, I re-ran my test case on 12.2 and checked the ASSM figures reported. The problem was that a switch from a 4KB or 8KB blocks size to a 16KB bblock size produced a performance disaster. A version of my test case and some timing results are available on Greg Rahn’s site.
In my test case I have 830,000 rows and do an update that sets column2 to column1 changing it from null to an 8-digit value. With a 16KB block size and PCTFREE set to a highly inappropriate value (in this case the default value of 10) this is what the new ASSM statistics looks like:
Name Value ---- ----- ASSM gsp:get free block 668,761 ASSM cbk:blocks examined 671,404 ASSM cbk:blocks marked full 2,643 ASSM gsp:L1 bitmaps examined 1,338,185 ASSM gsp:L2 bitmaps examined 672,413 ASSM gsp:Search all 332 ASSM gsp:Search hint 668,760 ASSM gsp:Search steal 332 ASSM gsp:bump HWM 332 ASSM wasted db state change 669,395
I’d love to know what the figures would have looked like if they had been available in the original Oracle 184.108.40.206 case (my guess is that the “blocks examined” statistic would have been in the order of hundreds of millions); they look fairly harmless in this case even though the database (according to some of the other instance activity stats) did roughly 10 times the work you might expect from a perfect configuration.
Even here, though, where the original catastrophic bug has been addressed, the ASSM stats give you an important clue: we’ve been doing a simple update so why have we even been looking for free space (get free block); even stranger, how come we had to examine 1.3M L1 bitmaps when we’ve only updated 830,000 rows surely the worst case scenario shouldn’t have been worse that 1 to 1; and then there’s that “wasted db state change” – I don’t understand exactly what that last statistic is telling me but when I’m worried about performance I tend to worry about anything that’s being wasted.
In passing – if you want to insert a single row into an unindexed table you can expect Oracle to examine the segment header, then an L2 bitmap block, then an L1 bitmap block to find a data block for the insert. (In rare cases that might be segment header, L3, L2, L1). There are then optimisation strategies for pinning blocks – the session will pin the L1 bitmap block briefly because it may have to check several data blocks it references because they may be full even though they are flagged as having space; similarly the session will pin the L2 bitmap block because it may need to mark an L1 bitmap block as full and check another L1 block. The latter mechanism probably explains why we have examined more L1 bitmaps than L2 bitmaps.
Finally, the full monty
Just a list of all the instance statistics that start with “ASSM”:
ASSM bg: segment fix monitor ASSM bg:create segment fix task ASSM bg:mark segment for fix ASSM bg:slave compress block ASSM bg:slave fix one segment ASSM bg:slave fix state ASSM cbk:blocks accepted ASSM cbk:blocks examined ASSM cbk:blocks marked full ASSM cbk:blocks rejected ASSM fg: submit segment fix task ASSM gsp:Alignment unavailable in space res ASSM gsp:L1 bitmaps examined ASSM gsp:L2 bitmap full ASSM gsp:L2 bitmaps examined ASSM gsp:L3 bitmaps examined ASSM gsp:Optimized data block rejects ASSM gsp:Optimized index block rejects ASSM gsp:Optimized reject DB ASSM gsp:Optimized reject l1 ASSM gsp:Optimized reject l2 ASSM gsp:Search all ASSM gsp:Search hint ASSM gsp:Search steal ASSM gsp:add extent ASSM gsp:blocks provided by space reservation ASSM gsp:blocks rejected by access layer callback ASSM gsp:blocks requested by space reservation ASSM gsp:bump HWM ASSM gsp:get free block ASSM gsp:get free critical block ASSM gsp:get free data block ASSM gsp:get free index block ASSM gsp:get free lob block ASSM gsp:good hint ASSM gsp:reject L1 ASSM gsp:reject L2 ASSM gsp:reject L3 ASSM gsp:reject db ASSM gsp:space reservation success ASSM gsp:use space reservation ASSM rsv:alloc from reserve ASSM rsv:alloc from reserve fail ASSM rsv:alloc from reserve succ ASSM rsv:clear reserve ASSM rsv:fill reserve ASSM wasted db state change