Oracle Block Clean Out Select Generates Redo
Oracle Block Clean Out
A
buffered copy of a block is clean if and only if the copy in memory is the same
as the version on disc that is on datafile. And if the copy in memory and the
version on disc differ (because something has been modified the in-memory copy)
then the buffer copy is “dirty”.
The dbwr has
two main reasons for writing and only one of those two is followed by a call to
free the buffer that’s just been written.
One
reason for writing is simply to keep the data files reasonably up to date; in
this case the buffered copy of the block changes from dirty to clean
but is not flushed from memory (and I think This process is known as
incremental checkpoint ). The other reason is when a session has been searching
for a free buffer, fails to find one soon enough, and calls DBWR
to make some free space in the buffer cache – and that’s the case when DBWR
may flush blocks from memory or, to be accurate, marks the buffer as free
after copying them to disc (and I’m not even sure that I’ve ever proved that
that’s really true). There are a couple of fairly well-known special cases
where blocks really flushed from disc after the write eg truncating tables,
putting tablespaces offline, flush buffer cache etc.
Commit Cleanout:
When you
modify some data you will make some in memory copy of blocks “dirty”. It is quite
possible that the database writer will copy those blocks to disc (marking the
buffers “clean”) before you issue your commit. When
you issue the commit, your session will update its transaction
table slot (which is in an undo segment header block),
generating a little redo to describe this action, and then
call the log writer (lgwr) to write.
Optionally,
your session may also revisit a few of the blocks it has modified (nominally up
to 10% of the buffer cache) and mark the associated ITL
(interested transaction list) entry in those blocks with the “commit SCN”.
This activity is called a commit cleanout. The commit
cleanout does not do a complete tidy up on the block (it leaves lock
bytes in place, for example), it simply ensures that the next process to see
the block knows that your transaction has committed when it committed. This
feature was introduced some around Oracle version 7.3 to reduce block pinging
in OPS (Oracle Parallel Server)
Note that
any of the blocks that had previously been written by dbwr will
have been made “dirty” again if your sessions does applies
a commit cleanout to them, so dbwr will have
to write them to disc again eventually. Although a commit cleanout
changes blocks it does not create any redo
to describe those changes and, strangely, it doesn’t report db
block gets for the block visits that apply those changes.
Block Cleanout / Delayed Block Cleanout:
It’s
possible that some of the blocks changed by your transaction were written to
disc by dbwr and were even flushed from the buffer
cache before you issued the commit. Your session will not
re-read these blocks in order to do a commit cleanout on them. In
fact, if your transaction was quite large and modified a lot of blocks, it’s
quite possible that your session will not even do a commit cleanout
on all of the blocks that are still in memory – Oracle doesn’t want to make the
user wait for a commit to complete, so it’s a bit lazy
about commit cleanout.
At some
later point in time another session may read one of those blocks and discover
that the ITL includes a transaction that has committed but
not been cleaned out. (It can work this out by cross-checking the ITL
entry with the relevant transaction table slot in the undo
segment header block).
This session will
read the commit SCN from the transaction table slot,
tidy up the block’s ITL entry, and clear all the related lock bytes.
(And it will do this for all committed transactions it finds on the
block). This process is known as block cleanout, and
because this full cleanout mechanism never takes place on the commit
it is more commonly known as delayed block cleanout.
The delayed
block cleanout operation changes the block so it generates redo
– which is why you may see redo being generated on a select
statement, especially after a very big update. (Interestingly, Oracle will
report db block changes as this happens – but doesn’t record the
block visits as db block gets.)
Delayed Logging Block Cleanout
Finally,
let’s go back to a block that was subject to commit cleanout. If
you query this block you can see the effects of the committed transaction, but
since the commit cleanout wrote the commit SCN
into its ITL slot you know when the transaction committed
and generally don’t need to do anything to tidy the block up as you read it.
However if you want to update the block you become responsible for finishing
the cleanout of the block – in fact you may even want to re-use that ITL
entry and update some of the rows which are still showing a lock byte. So, at
this point, you complete the block cleanout, and generate redo
that describes not only the changed you are now making but also the earlier commit
cleanout. This process is known as ‘delayed logging’
block cleanout – because the redo log for the commit cleanout
has been delayed until this moment.
References
http://jonathanlewis.wordpress.com/2009/06/16/clean-it-up/
Visit for more detail
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:275215756923
