Here is a link to a collection of scripts that can be used for some entertainment (well, it probably depends on your personal definition of "entertainment"...) regarding "Delayed Block Cleanout". These scripts are meant to be used in a playground environment - do not attempt to get them close to anything important as they might have some undesirable side-effects. Please read the comments in the file header description before attempting to run them.
The scripts allow to gain some insights into the different variants of delayed block cleanout and how to force an ORA-01555 error caused by it. The scripts among others allow to demonstrate that blocks generated via direct-path inserts are not "clean" and subject to a special kind of delayed block cleanout and therefore - although unlikely - can still cause an ORA-01555 error due to delayed block cleanout. This has been discussed recently here.
It is amazing how much effort has Oracle put into minimizing the overheads of storing the lock information in the block and still offering fast commits:
- There are fast cleanouts for small transactions that leave blocks in a "clean enough" state (which means that only the commit SCN of the ITL slot is updated but everything else is left behind for others to clean up) but do not generate redo although they modify a block. The block will be dirty and written (possibly again) by DBWR
- There are "immediate" (as part of consistent / current mode gets) and "deferred" (piggy-back as part of subsequent block modifications) delayed block cleanouts for blocks left behind by larger transactions that will tidy up the block including any lock bytes and ITL slot information. These will generate redo and dirty blocks.
- There is a special treatment of blocks that are generated by direct-path inserts that do not need to be cleaned up as blocks modified by conventional DML but still miss the "commit SCN" information from the ITL slots. Only a single block will be cleaned out (small amount of redo and block dirtied) and the "commit SCN" obtained will be "cached" in the session for further blocks accessed by this session
For further reading regarding the topic I recommend these links as starting points:
- "Clean it up" by Jonathan Lewis
- "Impact of Direct Reads on Delayed Block Cleanouts" by Christian Antognini
Some final food for thought regarding delayed block cleanout:
- Direct path reads performed by parallel execution slaves
- Adaptive serial direct reads introduced in Oracle 11g
- Readonly tablespaces
- Index blocks modified by DML
14 hours ago