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

Peter,

Jeremiah Wilton has already replied to my post along the same lines, which means I'm going back to the drawing board to run a load more tests to check what goes on.

My comments were largely based on the description in the Server Reference manual that this feature was designed to assist Parallel Server sites because whilst cleanout was applied on SELECTs, the block was not dirtied and therefore would not be pinged ...

From the 8.0 manual under delayed_logging_block_cleanout

In previous releases, when block cleanout was needed during a read to a current
block, extra cleanout redo records were generated and the block was dirtied. This
has been changed.

When a transaction commits, all blocks changed by the transaction are cleaned out
immediately. This cleanout performed at commit time is a “fast version” which
does not generate redo log records (delayed logging) and does not reping the block.
Most blocks will be cleaned out in this way, with the exception of blocks changed by
long running transactions.

During queries, therefore, the data block’s transaction information is normally up-to-
date and the frequency of needing block cleanout is greatly reduced. Regular block cleanouts are still needed when querying a block where the transactions are
still truly active, or when querying a block which was not cleaned out during
commit.

I can see plenty of scope of misunderstanding this description, and the sequencing of events does seem critical when trying to think it through, so I think I'm going to sit back and review my understanding carefully.

However - the last couple of lines do point out that the original problem may be due to (a) other current active transactions or (b) the current query is reading blocks which were updated by a very large transaction that could not clean out all the blocks it had affected.

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

P.S. re-reading your post, I wonder if when you said 'delayed block cleanout'
you simply meant that Oracle was writing out blocks that were on the dirty list
because it needed the space to read blocks for the query, and I took it to mean the transaction cleanout of each block.

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
>
>Jonathan Lewis wrote:
>
>> Peter,
>>
>> The version is 7.3.4 so the default clean-out mechanism means that
>> a select statement should not cause cleaned blocks to be written.
>> (delayed_logging_block_cleanouts = TRUE).
>>
>> Jonathan Lewis
>> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>>
>> Peter Sharman wrote in message <36B87B07.5A348719_at_us.oracle.com>...
>> >Satar
>> >
>> >You may be seeing the effects of delayed block cleanout, where your
SELECT
>> >statement is the first statement to hit blocks that have been updated
but
>> not
>> >written out yet.
>> >
>
>--
>
>
>Regards
>
>Pete
>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>Peter Sharman Email: psharman_at_us.oracle.com
>WISE Course Development Manager Phone: +1.650.607.0109 (int'l)
>Worldwide Internal Services Education (650)607 0109 (local)
>San Francisco
>
>"Controlling application developers is like herding cats."
>Kevin Loney, ORACLE DBA Handbook
>"Oh no it's not! It's much harder than that!"
>Bruce Pihlamae, long term ORACLE DBA
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>
Received on Thu Feb 04 1999 - 03:13:48 CST