At the end of * the last installment* we had seen a test case that caused Oracle to add a couple of redundant new extents to a LOB segment after one process deleted 3,000 LOBs and another four concurrent processes inserted 750 LOBs each a few minutes later (after the undo retention period had elapsed). To add confusion the LOBINDEX seemed to show that all the “reusable” chunks had been removed from the index which suggests that they should have been re-used. Our LOB segment started at 8,192 blocks, is currently at 8,576 blocks and is only using 8,000 of them.

How will things look if I now connect a new session (which might be associated with a different freepool), delete the oldest 3,000 LOBs, wait a little while, then get my original four sessions to do their concurrent inserts again ? And what will things look like after I’ve repeated this cycle several times ?

I had to drop the tables from my original test since writing the previous article, so the following results start from recreating the whole test from scratch and won’t align perfectly with the previous sets of results. Here’s what the index treedump looked like after going through the serial delete / concurrent insert cycle 12 times:

----- begin tree dump branch: 0x1800204 25166340 (0: nrow: 71, level: 1) leaf: 0x1800223 25166371 (-1: nrow: 0 rrow: 0) leaf: 0x1800227 25166375 (0: nrow: 0 rrow: 0) leaf: 0x1800236 25166390 (1: nrow: 0 rrow: 0) leaf: 0x180023d 25166397 (2: nrow: 63 rrow: 63) leaf: 0x1800206 25166342 (3: nrow: 81 rrow: 81) leaf: 0x1800225 25166373 (4: nrow: 81 rrow: 81) leaf: 0x1800229 25166377 (5: nrow: 81 rrow: 81) leaf: 0x180020a 25166346 (6: nrow: 81 rrow: 81) leaf: 0x180020e 25166350 (7: nrow: 81 rrow: 81) leaf: 0x1800212 25166354 (8: nrow: 76 rrow: 76) leaf: 0x1800216 25166358 (9: nrow: 81 rrow: 81) leaf: 0x180021a 25166362 (10: nrow: 81 rrow: 81) leaf: 0x180021e 25166366 (11: nrow: 81 rrow: 81) leaf: 0x1800222 25166370 (12: nrow: 126 rrow: 126) leaf: 0x1800266 25166438 (13: nrow: 0 rrow: 0) leaf: 0x180025e 25166430 (14: nrow: 39 rrow: 39) leaf: 0x1800262 25166434 (15: nrow: 81 rrow: 81) leaf: 0x1800243 25166403 (16: nrow: 81 rrow: 81) leaf: 0x1800261 25166433 (17: nrow: 76 rrow: 76) leaf: 0x1800269 25166441 (18: nrow: 81 rrow: 81) leaf: 0x180026d 25166445 (19: nrow: 81 rrow: 81) leaf: 0x1800271 25166449 (20: nrow: 81 rrow: 81) leaf: 0x1800275 25166453 (21: nrow: 81 rrow: 81) leaf: 0x1800279 25166457 (22: nrow: 81 rrow: 81) leaf: 0x180027d 25166461 (23: nrow: 81 rrow: 81) leaf: 0x180024a 25166410 (24: nrow: 118 rrow: 118) leaf: 0x1800263 25166435 (25: nrow: 0 rrow: 0) leaf: 0x180024c 25166412 (26: nrow: 0 rrow: 0) leaf: 0x1800254 25166420 (27: nrow: 0 rrow: 0) leaf: 0x1800264 25166436 (28: nrow: 1 rrow: 0) leaf: 0x1800274 25166452 (29: nrow: 2 rrow: 0) leaf: 0x180027c 25166460 (30: nrow: 2 rrow: 0) leaf: 0x180025d 25166429 (31: nrow: 2 rrow: 0) leaf: 0x1800241 25166401 (32: nrow: 2 rrow: 0) leaf: 0x1800245 25166405 (33: nrow: 2 rrow: 0) leaf: 0x1800265 25166437 (34: nrow: 1 rrow: 0) leaf: 0x1800251 25166417 (35: nrow: 3 rrow: 0) leaf: 0x1800249 25166409 (36: nrow: 4 rrow: 0) leaf: 0x1800242 25166402 (37: nrow: 1 rrow: 0) leaf: 0x1800255 25166421 (38: nrow: 2 rrow: 0) leaf: 0x1800259 25166425 (39: nrow: 3 rrow: 0) leaf: 0x1800246 25166406 (40: nrow: 1 rrow: 0) leaf: 0x1800214 25166356 (41: nrow: 38 rrow: 0) leaf: 0x1800218 25166360 (42: nrow: 81 rrow: 0) leaf: 0x180021c 25166364 (43: nrow: 81 rrow: 0) leaf: 0x1800220 25166368 (44: nrow: 0 rrow: 0) leaf: 0x180022d 25166381 (45: nrow: 26 rrow: 26) leaf: 0x1800231 25166385 (46: nrow: 81 rrow: 81) leaf: 0x1800219 25166361 (47: nrow: 81 rrow: 81) leaf: 0x1800235 25166389 (48: nrow: 81 rrow: 81) leaf: 0x1800239 25166393 (49: nrow: 81 rrow: 81) leaf: 0x180022c 25166380 (50: nrow: 81 rrow: 81) leaf: 0x180023c 25166396 (51: nrow: 81 rrow: 81) leaf: 0x180022b 25166379 (52: nrow: 81 rrow: 81) leaf: 0x180022f 25166383 (53: nrow: 81 rrow: 81) leaf: 0x1800233 25166387 (54: nrow: 81 rrow: 81) leaf: 0x1800237 25166391 (55: nrow: 81 rrow: 81) leaf: 0x180023b 25166395 (56: nrow: 79 rrow: 79) leaf: 0x180023f 25166399 (57: nrow: 81 rrow: 81) leaf: 0x1800208 25166344 (58: nrow: 81 rrow: 81) leaf: 0x180020c 25166348 (59: nrow: 81 rrow: 81) leaf: 0x1800210 25166352 (60: nrow: 120 rrow: 120) leaf: 0x180021d 25166365 (61: nrow: 0 rrow: 0) leaf: 0x1800248 25166408 (62: nrow: 21 rrow: 21) leaf: 0x1800268 25166440 (63: nrow: 81 rrow: 81) leaf: 0x180026c 25166444 (64: nrow: 152 rrow: 152) leaf: 0x180026b 25166443 (65: nrow: 152 rrow: 152) leaf: 0x180026f 25166447 (66: nrow: 152 rrow: 152) leaf: 0x1800273 25166451 (67: nrow: 152 rrow: 152) leaf: 0x1800277 25166455 (68: nrow: 152 rrow: 152) leaf: 0x180027b 25166459 (69: nrow: 66 rrow: 66) ----- end tree dump

As usual I’ve split the treedump into the sections that reflect the freepools, each of which could consist of two parts the LOBs (key values starting with even numbers) and the “reusable chunks” (key values starting with odd numbers). The dump suggests that things have worked well: as you can see it’s grown a few blocks after my 12 cycles but there are only 6 sections (not the full 8 that might be there), and only a few leaf blocks showing “empty” (rrows = 0). As “reusable” sections have appeared the index has grown a little, then the reusable entries have been taken off the index and the index has shrunk a bit; you can even see that freepool 3 (the highest numbered one) is still showing a pattern of 152 LOBs indexed per block – this is despite the fact that at one point a reusable section for freepool 3 (00 07) appeared above this section and then disappeared as those reusable chunks were reclaimed.

All in all the index seems to be behaving **extremely** well, with only a little growth and (probably temporarily) a couple of little glitches of empty leaf blocks.

Here’s the dump of the (slightly edited) “col 0” values to confirm where the freepool breaks were –

0: col 0; len 10; (10): 00 00 00 01 00 00 09 df 2c cc 1: col 0; len 10; (10): 00 00 00 01 00 00 09 df 31 61 2: col 0; len 10; (10): 00 00 00 01 00 00 09 df 33 61 3: col 0; len 9; ( 9): 00 00 00 01 00 00 09 df 36 4: col 0; len 10; (10): 00 00 00 01 00 00 09 df 37 1e 5: col 0; len 10; (10): 00 00 00 01 00 00 09 df 38 37 6: col 0; len 10; (10): 00 00 00 01 00 00 09 df 39 1e 7: col 0; len 10; (10): 00 00 00 01 00 00 09 df 3a 37 8: col 0; len 10; (10): 00 00 00 01 00 00 09 df 3b 50 9: col 0; len 9; ( 9): 00 00 00 01 00 00 09 df 3c 10: col 0; len 10; (10): 00 00 00 01 00 00 09 df 3d 4b 11: col 0; len 9; ( 9): 00 00 00 01 00 00 09 df 3e 12: col 0; len 10; (10): 00 00 00 01 00 00 09 df 3e e7 13: col 0; len 10; (10): 00 02 00 01 00 00 09 df 25 9b 14: col 0; len 10; (10): 00 02 00 01 00 00 09 df 32 a0 15: col 0; len 10; (10): 00 02 00 01 00 00 09 df 34 1d 16: col 0; len 10; (10): 00 02 00 01 00 00 09 df 36 c6 17: col 0; len 10; (10): 00 02 00 01 00 00 09 df 39 3d 18: col 0; len 9; ( 9): 00 02 00 01 00 00 09 df 3d 19: col 0; len 10; (10): 00 02 00 01 00 00 09 df 3f 52 20: col 0; len 10; (10): 00 02 00 01 00 00 09 df 40 cf 21: col 0; len 10; (10): 00 02 00 01 00 00 09 df 41 20 22: col 0; len 10; (10): 00 02 00 01 00 00 09 df 41 71 23: col 0; len 10; (10): 00 02 00 01 00 00 09 df 41 c2 24: col 0; len 10; (10): 00 02 00 01 00 00 09 df 42 13 25: col 0; len 10; (10): 00 03 57 bc ba 2f 00 00 00 00 26: col 0; len 10; (10): 00 03 57 bc ba 2f 00 00 00 00 27: col 0; len 10; (10): 00 03 57 bc ba 2f 00 00 00 00 28: col 0; len 10; (10): 00 03 57 bc ba 2f 00 00 00 00 29: col 0; len 10; (10): 00 03 57 bc ba 2f 00 00 00 00 30: col 0; len 10; (10): 00 03 57 bc ba 2f 00 00 00 00 31: col 0; len 10; (10): 00 03 57 bc ba 2f 00 00 00 00 32: col 0; len 10; (10): 00 03 57 bc ba 2f 00 00 00 00 33: col 0; len 10; (10): 00 03 57 bc ba 2f 00 00 00 00 34: col 0; len 10; (10): 00 03 57 bc ba 2f 00 00 00 00 35: col 0; len 10; (10): 00 03 57 bc ba 2f 00 00 00 00 36: col 0; len 10; (10): 00 03 57 bc ba 2f 00 00 00 00 37: col 0; len 10; (10): 00 03 57 bc ba 2f 00 00 00 00 38: col 0; len 10; (10): 00 03 57 bc ba 2f 00 00 00 00 39: col 0; len 10; (10): 00 03 57 bc ba 2f 00 00 00 00 40: col 0; len 10; (10): 00 03 57 bc ba 2f 00 00 00 00 41: col 0; len 10; (10): 00 04 00 01 00 00 09 df 26 52 42: col 0; len 10; (10): 00 04 00 01 00 00 09 df 2a 27 43: col 0; len 10; (10): 00 04 00 01 00 00 09 df 2a dc 44: col 0; len 10; (10): 00 04 00 01 00 00 09 df 2b 2d 45: col 0; len 10; (10): 00 04 00 01 00 00 09 df 31 34 46: col 0; len 10; (10): 00 04 00 01 00 00 09 df 33 15 47: col 0; len 10; (10): 00 04 00 01 00 00 09 df 34 92 48: col 0; len 10; (10): 00 04 00 01 00 00 09 df 34 e3 49: col 0; len 10; (10): 00 04 00 01 00 00 09 df 35 34 50: col 0; len 10; (10): 00 04 00 01 00 00 09 df 35 85 51: col 0; len 10; (10): 00 04 00 01 00 00 09 df 35 d6 52: col 0; len 10; (10): 00 04 00 01 00 00 09 df 36 27 53: col 0; len 10; (10): 00 04 00 01 00 00 09 df 38 6c 54: col 0; len 10; (10): 00 04 00 01 00 00 09 df 38 ef 55: col 0; len 10; (10): 00 04 00 01 00 00 09 df 3a d0 56: col 0; len 10; (10): 00 04 00 01 00 00 09 df 3c 4d 57: col 0; len 9; ( 9): 00 04 00 01 00 00 09 df 3d 58: col 0; len 10; (10): 00 04 00 01 00 00 09 df 3e 4b 59: col 0; len 10; (10): 00 04 00 01 00 00 09 df 3f 96 60: col 0; len 10; (10): 00 04 00 01 00 00 09 df 40 7d 61: col 0; len 10; (10): 00 05 57 bc b9 db 00 00 00 00 62: col 0; len 10; (10): 00 06 00 01 00 00 09 df 32 5b 63: col 0; len 10; (10): 00 06 00 01 00 00 09 df 33 a6 64: col 0; len 10; (10): 00 06 00 01 00 00 09 df 36 4f 65: col 0; len 10; (10): 00 06 00 01 00 00 09 df 38 13 66: col 0; len 10; (10): 00 06 00 01 00 00 09 df 3a 09 67: col 0; len 10; (10): 00 06 00 01 00 00 09 df 3b cd 68: col 0; len 10; (10): 00 06 00 01 00 00 09 df 3d 91 69: col 0; len 10; (10): 00 06 00 01 00 00 09 df 3f 23

As you can see at leaf block 61 we didn’t quite empty the reusable list from freepool 2 (00 05 = 2 * 2 + 1), and leaf blocks 25 to 40 tell us that freepool 1 (00 03 = 2 * 1 + 1) was the freepool used on the last big delete. Despite the odd little glitches it looks as if this strategy of *“deleted LOBs go to my process’ freepool”* seems to do a good job of reusing index space.

But there IS a problem. Here’s the output from a script I wrote using the * dbms_space* package to show how space in the LOB segment has been used:

Unformatted : 4,508 / 36,929,536 Freespace 1 ( 0 - 25% free) : 0 / 0 Freespace 2 ( 25 - 50% free) : 0 / 0 Freespace 3 ( 50 - 75% free) : 0 / 0 Freespace 4 ( 75 - 100% free) : 0 / 0 Full : 8,000 / 65,536,000 Segment Total blocks: 12672 Object Unused blocks: 0

The LOB segment has grown from an initial 8,192 blocks with a few unformatted blocks and 8,000 used blocks (2 blocks per LOB, 4,000 LOBs) to 12,672 blocks with 4,508 blocks unformatted. (The difference between Full + Unformatted and Segment Total blocks is the set of bitmap space management blocks for the segment). After only 12 cycles we have “leaked” an overhead of 50% of our real data space – maybe this helps to explain why the client that started me down this set of blogs has seen Oracle allocate 700GB to hold just 200GB of LOBs.

The tablespace is declared as locally managed with 1MB uniform extents and automatic segment space management. By writing a simple script I can get Oracle to write a script to dump the first block of each extent – and they will all be Level 1 bitmap space management blocks. Running * grep* against the trace file I can pick out the lines that tell me how many data blocks are mapped by the bitmap and how many of them have been formatted or not. This is the result (I have 99 extents in the segment – 99 * 128 = 12,672):

unformatted: 0 total: 64 first useful block: 4 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 62 total: 64 first useful block: 2 unformatted: 62 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 24 total: 64 first useful block: 2 unformatted: 52 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 26 total: 64 first useful block: 2 unformatted: 42 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 42 total: 64 first useful block: 2 unformatted: 0 total: 64 first useful block: 2 unformatted: 26 total: 64 first useful block: 2 unformatted: 62 total: 64 first useful block: 2 unformatted: 62 total: 64 first useful block: 2 unformatted: 62 total: 64 first useful block: 2 unformatted: 62 total: 64 first useful block: 2 unformatted: 62 total: 64 first useful block: 2 unformatted: 62 total: 64 first useful block: 2 unformatted: 62 total: 64 first useful block: 2 unformatted: 62 total: 64 first useful block: 2 unformatted: 62 total: 64 first useful block: 2 unformatted: 62 total: 64 first useful block: 2 unformatted: 62 total: 64 first useful block: 2 unformatted: 62 total: 64 first useful block: 2 unformatted: 62 total: 64 first useful block: 2 unformatted: 62 total: 64 first useful block: 2 unformatted: 127 total: 128 first useful block: 1 unformatted: 2 total: 128 first useful block: 1 unformatted: 2 total: 128 first useful block: 1 unformatted: 1 total: 128 first useful block: 1 unformatted: 125 total: 128 first useful block: 1 unformatted: 125 total: 128 first useful block: 1 unformatted: 125 total: 128 first useful block: 1 unformatted: 1 total: 128 first useful block: 1 unformatted: 2 total: 128 first useful block: 1 unformatted: 2 total: 128 first useful block: 1 unformatted: 125 total: 128 first useful block: 1 unformatted: 125 total: 128 first useful block: 1 unformatted: 108 total: 128 first useful block: 1 unformatted: 1 total: 128 first useful block: 1 unformatted: 2 total: 128 first useful block: 1 unformatted: 2 total: 128 first useful block: 1 unformatted: 105 total: 128 first useful block: 1 unformatted: 96 total: 128 first useful block: 1 unformatted: 125 total: 128 first useful block: 1 unformatted: 2 total: 128 first useful block: 1 unformatted: 2 total: 128 first useful block: 1 unformatted: 1 total: 128 first useful block: 1 unformatted: 125 total: 128 first useful block: 1 unformatted: 125 total: 128 first useful block: 1 unformatted: 38 total: 128 first useful block: 1 unformatted: 2 total: 128 first useful block: 1 unformatted: 1 total: 128 first useful block: 1 unformatted: 2 total: 128 first useful block: 1 unformatted: 65 total: 128 first useful block: 1 unformatted: 98 total: 128 first useful block: 1 unformatted: 125 total: 128 first useful block: 1 unformatted: 0 total: 128 first useful block: 1 unformatted: 0 total: 128 first useful block: 1 unformatted: 0 total: 128 first useful block: 1 unformatted: 125 total: 128 first useful block: 1 unformatted: 125 total: 128 first useful block: 1

You’ll notice that for the first 63 extents Oracle says there are 64 blocks mapped by the bitmap with the first useful block at block 2, thereafter it says there are 128 blocks and the first useful block is block 1 (Oracle is counting from zero). While Oracle thinks the * segment* is “quite small” it allocates two level 1 bitmap blocks per 1MB extent and I’ve only dumped the first block from each extent; but when the segment reaches 64MB Oracle decides that it’s getting pretty big and there’s no point in wasting space so changes to using a single level 1 bitmap block per 1MB extent. It’s just one of those tiny details you discover when you happen to look a little closely and how things work. (On a much larger test with 8MB uniform extents Oracle got to the point where it was using one L1 bitmap block for the whole 8MB.)

There’s a fascinating pattern in the later extents of 3 full extents followed by 3 empty extents – my first guess had been that Oracle was allocating new extents but not using them, but clearly that’s not right, it’s removing “reusable chunks” from the index and then not re-using them but using the new extents instead (some of the time). Something is seriously wrong with the way Oracle is handling the “reusable chunks” part of the index. With a little luck it’s some nasty side effect of the “one process delete / multiple process insert” strategy we have adopted, so: (a) we need to repeat the entire experiment with a concurrent delete mechanism and (b) we need to think about how we might re-engineer a REALLY BIG system that has followed this unfortunate strategy for a long time. Of course if (a) turns out to be a disaster as well we don’t really need to think too hard about (b) until we have discovered a good way of dealing with our rolling pattern of inserts and deletes.

Some (minimum effort, we hope) ideas we will have to look at for (b):

- Oracle has an option to change the freepools count on a LOB segment – do we need to use it, how much work would it entail, would it require downtime
- Oracle has an option to “rebuild” the freepools on a LOB segment
- We can always try the “shrink space compact” option on the LOB
- Should we just rebuild (move) the LOB segment – and use a larger extent size while we’re at it
- Should we recreate the table and change the LOB to Securefiles as we do so – and do all the testing all over again
- If we’re deleting old data on such a regular pattern should we try to bypass the deletes by partitioning the table in some clever way

TO BE CONTINUED.

- September 2020 (23)
- August 2020 (54)
- July 2020 (38)
- June 2020 (49)
- May 2020 (50)
- April 2020 (64)
- March 2020 (62)
- February 2020 (59)
- January 2020 (90)
- December 2019 (48)
- November 2019 (47)
- October 2019 (64)
- September 2019 (40)
- August 2019 (42)
- July 2019 (45)
- June 2019 (37)
- May 2019 (43)
- April 2019 (49)
- March 2019 (61)
- February 2019 (25)
- January 2019 (35)
- December 2018 (39)
- November 2018 (55)
- October 2018 (75)
- September 2018 (38)
- August 2018 (89)
- July 2018 (58)
- June 2018 (61)
- May 2018 (66)
- April 2018 (40)

## Recent comments

2 years 34 weeks ago

2 years 46 weeks ago

2 years 50 weeks ago

2 years 51 weeks ago

3 years 3 weeks ago

3 years 25 weeks ago

3 years 45 weeks ago

4 years 22 weeks ago

5 years 7 weeks ago

5 years 7 weeks ago