Delayed block
cleanout and Oracle performance
Delayed block cleanouts relate to copies of data block that are made to
enforce "read consistency". In plain English, when a transaction (DML
insert, update, delete) is committed, Oracle "marks" the block as changes
before it is truly changed, because this required a time consuming write to
the back-end tablespace storage (usually disk, which is very slow).
Deferred (delayed) block cleanouts are the number of times cleanout
records are deferred within the data buffer.
The "delayed block
cleanout" mechanism generates redo on a select statement, and it is visible
through the statistics delayed block cleanout in v$sessstat and
v$sysstat.
- "cleanouts only - consistent read gets"
-
"cleanouts and rollbacks - consistent read gets"
Steve Adams
notes this that you can force block cleanouts with a commit, but
delayed block cleanouts are more efficient:
"Delayed block
cleanout is performed by the next reader of the block, even if it is
just a query. The block is read into the cache as a current
mode block and cleaned out.
If a query is being performed, a
consistent read mode clone of the block is made using another block
buffer, and if necessary rolled back to the snapshot SCN for the query.
Oracle's row-level locking mechanism maintains transaction and
row lock information together with the data in the data blocks. After a
transaction has committed, that row-level locking information can be
cleaned out.
The cleanout can
be delayed indefinitely, but it is most efficient if it is done at
commit time. Therefore, when a transaction commits, it revisits the
blocks that it has modified most recently and attempts to clean out its
row-level locking information in those blocks.
This commit
cleanout is limited to 10% of the cache and is subject to numerous
restrictions. In particular, the blocks must not yet have been written
to disk by DBWn or modified by another transaction.
While commit
cleanouts are more efficient than delayed block cleanouts, their cost in
terms of CPU usage and buffer cache concurrency control is far from
trivial. If frequent commits cause the same blocks to be cleaned out
repeatedly by the commit cleanout mechanism, then the total cost of the
commit cleanouts can easily exceed the cost of equivalent delayed block
cleanouts."
