Re: URGENT! Writes to Disk while Doing a SELECT statement?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 4 Feb 1999 14:17:40 -0000
Message-ID: <918137845.18255.0.nnrp-02.9e984b29@news.demon.co.uk>

Peter,

It took some time, and I know I haven't covered all the bases yet, but from some simple tests involving block dumps and checking flags on buffer headers, this is what happens, starting with a clean table.

Emulate a long running transaction, and old-style cleanout

Update one row

The block goes dirty, and a few seconds late will be written out to disc by a DBWR timeout, so becomes clean again.

Flush the buffer completely
Commit the transaction

Since the table block is not in the buffer, the fast commit cannot happen, and the block (on disc) is unchanged.

Select a row from the block, and block cleanout takes place as the block is loaded back into the buffer. The ITL entry is updated (to flag C) to show a completed transaction with its scn, and the 'lock flag' on the row entry identifying the relevant ITL is set back to 0. The block is dirty and gets written out a few seconds later on the DBWR timeout.

b) A normal 'short' transaction:
Update the row

The block goes dirty, and a few seconds late will be written out to disc by a DBWR timeout, so becomes clean again. Wait for this to happen.

Commit the transaction

Fast commit takes place on the block - the ITL is marked to show the commit SCN, but the ITL flag is set to U, not C. The big difference difference though is that the 'lock flag' on the row is NOT changed to 0, and still points to the ITL.

The block is marked as dirty, and gets written out a few seconds later.

Select from the table -

The 'lock flag' on the row does not change, and the block clean out is not completed, the 'lock flag' on the row still points to the ITL.

Update another row in the block,

The ITL entry gets reused, and the block clean out is completed for the first transaction. Generating the necessary series of log entries as it goes.

Net Effect:

In cases where there are lots of small transactions going on, a COMMIT actually marks all the affected blocks, so that a select on another instance doesn't have to do it and cause pinging. However, to make the fast COMMIT as cheap as possible, Oracle does not do the complete clean-out, and in fact may NEVER complete the cleanout if no further transactions hit the same block again.

In the case of the original question - if the table being read was initially created by some very long transactions that flushed loads of blocks out of the buffer BEFORE the commit, then a subsequent read would be doing 'old style' block cleanout and generating lots of writes. Otherwise a select should NOT generate writes.

Those of you with long memories may, by the way, remember the literature for earlier versions of Oracle describing how it did NOT re-visit blocks when it did a commit and that this was a cunning design feature that made for more efficient end-user processing - O tempora, O mores !

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Peter Sharman wrote in message <36B8B7A6.BC5C3092_at_us.oracle.com>...
>Jonathan
>
>I'm somewhat confused by what you said. DELAYED_LOGGING_BLOCK_CLEANOUTS
turns
>the delayed block cleanout feature on or off. Keeping this feature set to
TRUE
>sets a fast path, no logging block cleanout at commit time. Logging the
block
>cleanout occurs at the time of a subsequent change to the block.
>
>When Oracle commits a transaction, each block that the transaction changed
is
>not immediately marked with the commit time. This is done later, on demand,
>when the block is read or updated.
>
>In other words, the default mechanism means that a select statement SHOULD
>cause cleaned blocks to be written, at least to my understanding.
>
>I'd be glad to be corrected if I'm wrong though!
>
>Pete
>
Received on Thu Feb 04 1999 - 08:17:40 CST