Clean it up

There is some confusion about the expression “clean” in Oracle circles, so I thought I’d write a short note to explain the different ways in which the word may be applied to Oracle blocks. There are five terms to consider:

1. clean
2. commit cleanout
3. block cleanout
4. delayed block cleanout
5. delayed logging block cleanout

Clean:

A block (or, to be strictly accurate, a buffered copy of a block) is “clean” if and only if the copy in memory is the same as the version on disc. Conversely, if the copy in memory and the version on disc differ (presumably because something has modified the in-memory copy) then the buffer copy is “dirty”. Moreover, we have to remember that the concepts of “clean” and “dirty” are only relevant to the current copy of the block.

You have to be  a little careful when checking for dirty blocks, though. The buffer header structure (x$bh) includes a column called flag which uses a bit string to tell us about the state of the buffer. Bit zero in this flag is the “buffer_dirty” bit so we can easily check which buffers are considered dirty. There’s a little trap, though. Oracle only needs to write dirty buffers that are also “current” versions (CUR) and you will find that it doesn’t reset the “buffer_dirty” flag when it makes a read-consistent (CR) copy of the block.  So if you’re trying to count the number of dirty buffers in x$bh don’t forget to restrict your count to those where the “state” column is set to 1 or 2 (XCUR and SCUR)

There is a common misunderstanding about what happens when DBWR (the database writer) writes a block to disc. I have often seen people say that the copy of the block is “flushed” from the cache when the write takes place (i.e. implying that the block disappears from the cache). Most of the time this is not true, the dbwr has two main reasons for writing and only one of those (I believe) 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. 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 in that case DBWR may flush blocks from memory or, to be accurate, mark the buffers as free after copying the blocks to disc (and I’m not sure that I’ve ever proved that that’s exactly true). There are a couple of fairly well-known special cases where blocks really are flushed from disc after the write: truncating tables, putting tablespaces offline, and the explicit “flush buffer_cache” come to mind.

Commit Cleanout:

When you modify some data you will make some buffered 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 some of the blocks it has modified (nominally as many as 10% of the buffer cache) and mark the associated ITL (interested transaction list) entry in those blocks with the “commit SCN”. It is this activity that 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 and exactly when it committed. This feature was introduced some around Oracle version 7.3 to reduce block pinging in OPS  (Oracle Parallel Server – the precursor to RAC).

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 looks as if it has not committed when it actually 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 any committed transactions it finds in 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 your session wants to update the block it becomes 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, your session completes the block cleanout and generates redo that describes not only the changes 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 previous commit cleanout has been delayed until this moment.

There are lots more tiny bits of information, odd behaviour in special cases, and peculiarities of statistics reporting that we could examine, but the extra details are unlikely to add value to the explanations above and they’re the sort of details that are likely to change as newer versions of Oracle appear.