Deleted Index Entries Part III (Slip Away) June 23, 2008

I’ve already looked at the most common example of when Oracle will automatically clean out deleted index entries, that being any subsequent insert into a leaf block will clean out the deleted entries that may exist from the associated leaf block.

Another example of Oracle automatically removing deleted index entries is that associated with a variation of delayed block cleanout. If an Oracle index block with deleted index entries is written to disk before the associated transaction performing the index delete operation is committed, the next time the index block is accessed, Oracle will not only clean out the transaction details from the index block (such as the lock byte) but the deleted index entries themselves may also be cleaned out as well.

This scenario is most likely to occur during large or long running transaction operations (such as batch operations) where many rows are likely to be accessed and/or modified and the associated modified index blocks may get aged out of the buffer cache and written to disk before the transaction ends via the COMMIT.

Note this delayed clean out does not require the index block to be accessed via a subsequent DML operation, even a simple SELECT statement will be sufficient to perform the necessary clean out of deleted index entries.

To illustrate this behaviour, basically create a table with a bunch of rows, deleted some of them but flush the buffer cache prior to issuing the commit on the delete.

At this point, we flush the associated blocks to disk to simulate a large or long running transaction is which blocks may be aged from the buffer cache and written to disk before the COMMIT is performed.

SQL> ALTER SESSION SET EVENTS ‘immediate trace name flush_cache';

Session altered.

in 9i, or since 10g:

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

It’s only at this point after the blocks are flushed to disk that the COMMIT is performed.

SQL> COMMIT;

Commit complete.

Once the index blocks are subsequently accessed, we notice the deleted index entries may have already been cleaned out …

NOTE: The following results do not consistently occur if the index consists of just a single block (the root block is a “special” case), but does appear to be more consistent if the index has a blevel of one or more (as in the demo) and as would be more typical with indexes involved in long running transactions.

A view of INDEX_STATS after a ANALYZE … VALIDATE STRUCTURE command will show the following:

What’s possibly gone wrong is that I’ve found a number of scenarios and Oracle versions where the buffer cache isn’t always cleared as expected and/or the session doesn’t perform the necessary physical I/Os for this to work.

Rather than use the same session to flush the buffer cache, try and use another session and just make sure the blocks have indeed been flushed from the cache and need to be re-read subsequent to the commit.

Thank you very much indeed, I ran the demo on a windows 10.2.0.3 (close) database and have managed to reproduce your results. However, after playing around with it for a while, I have also managed to make it work perfectly as well on the same configuration so the results are clearly not consistent with it working sometimes and not at other times.

I’m not entirely sure at this stage what I do differently although I notice is some of my tests after I flush the buffer cache and the buffers are freed, a read forcing the index still produces no PIOs so I suspect it has something to do with the flushing mechanism not forcing PIOs as it should and hence the clean outs not occuring.

I need to do some more digging but thank you again so much for your input, it’s much appreciated.

I’ve modified the post to suggest the demo as described may only work.

I’ve modified the demo so that more rows are inserted into the table and the associated index consists of more than just a single block, blevel 0 index.

The root block is a special index block in that it never changes so that Oracle always knows from where to start an index scan, without having to visit the segment header first. I think this has something to do with the delayed block cleanout not working in some environments, in some cirumstances as details remain cached regardless.

By having a blevel of 1 or more, the effects of delayed block cleanout appear to be more consistent. I would be intertesed in knowing whether by creating such an index, you get the same results as well.