Re: delayed logging block cleanout -- Any other comments?
- From: "Diego Cutrone" <diegocutrone@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 27 May 2004 13:40:37 -0700
Hi Jonathan,
Thanks for answering.
Oracle 7 behaves exactly as you have described.
This is the part of the process that was weird for me:
> Going back to the beginning - if on the commit, the block was
> not available in memory, then it would not be cleaned at all.
> On the next read into memory (whether for query only or for
> update) the block would be cleaned. If the read was query
> only, the redo generated would be the full cleanout redo, and
> the block would be dirtied.
I thought that if an "uncleaned block" had to be read by a query, the block
was *not dirtied* and no redo was generated by that session unless
delayed_logging_block_cleanout=FALSE (in Oracle 7) . I thought that was the
way it worked until I tested it yesterday.
Even if you read Steve comments at http://www.ixora.com.au/q+a/cr.htm, you
can see he's saying the same thing, specially here:
"
However, if you have data that is queried intensively, but seldom changed,
then it may be beneficial to temporarily set the parameter to FALSE and
force a serial full table scan after each change. The full scan has to be
serial, because parallel scans do direct reads, so the cleanouts are done in
private buffers in the PGAs of the parallel query slaves, where the cleaned
out blocks cannot be written back to disk by DBWn.
"
I used to believe that the benefit of setting this to FALSE in a single
instance was that I was improving queries response time by letting only the
first
query perform the cleanout and avoiding the latter queries to do the same
thing over and over again.
But now I am wondering if there's really any benefit on setting
delayed_logging_block_cleanout=FALSE in a single Oracle7, 8.0 instance...
Thank you
Diego.
----- Original Message -----
From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, May 27, 2004 7:33 AM
Subject: Re: delayed logging block cleanout -- Any other comments?
>
> It's a long time ago, but I remember when I first read
> Steve's note I didn't think it was complete. My tests
> had suggested that
>
> On "commit cleanout" an in-memory block could have
> its ITL updated with the SCN, and this buffer would then
> be dirty and get written out. The redo for the cleanout
> was NOT logged, however, so the block on disc was in
> a state that was NOT described by the redo.
>
> The next transaction to update to that block would generate
> UNDO that made it look as if the block had previously
> been cleaned out, so that if the next transaction rolled back,
> the block would be left in a state that looked as if the previous
> transaction had done a full clean out. At no stage would there
> be any REDO generated that would otherwise move the block
> from 'not cleaned' to 'clean'. (Hence "delayed logging" is a
> euphemism for "never logged").
>
> Going back to the beginning - if on the commit, the block was
> not available in memory, then it would not be cleaned at all.
> On the next read into memory (whether for query only or for
> update) the block would be cleaned. If the read was query
> only, the redo generated would be the full cleanout redo, and
> the block would be dirtied. If the read was for update, the
> UNDO for the update would be as in the first example - the
> UNDO that would take the block back to a clean state that
> had never actually existed.
>
> I don't have an old version to repeat the tests on - but perhaps
> the change in your results is related to the size of the buffer,
> and the size of the update which would have some effect on
> how many blocks would be subject to commit-time partial
> cleaning, and how many blocks would still be in memory in
> and cleanable anyway.
>
>
> Regards
>
> Jonathan Lewis
>
> http://www.jlcomp.demon.co.uk
>
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ
>
> http://www.jlcomp.demon.co.uk/seminar.html
> Optimising Oracle Seminar - schedule updated May 1st
>
>
> ----- Original Message -----
> From: "Diego Cutrone" <diegocutrone@xxxxxxxxxxxx>
> To: <oracle-l@xxxxxxxxxxxxx>
> Sent: Thursday, May 27, 2004 6:10 PM
> Subject: Re: delayed logging block cleanout -- Any other comments?
>
>
> Has anybody tested this in an Oracle 7/8.0 database?
>
> I have tested this yesterday and found incompatible results according to
> Steve's comments:
>
> "If delayed_logging_block_cleanouts is TRUE, which is the
> default in 7.3 and 8.0, the logging of this redo is delayed until another
> change is made to the current buffer. The cleanout itself does not mark
the
> buffer as dirty or log the redo for its changes. The next change to the
> buffer marks the buffer as dirty and logs redo for the cleanout as well
as
> its own changes.
> " (from http://www.ixora.com.au/q+a/cr.htm)
>
> In an Oracle 7.3.4 instance (d_b_c_o = TRUE) , whenever I execute a query
> against some table with uncleaned blocks, they get clean out !!
> (I can even see redo being generated by the session execting the query in
> v$sesstat.)
>
> I had performed the same test a couple of years ago against another O7
> instance and it worked out just like Steve says above, I don't know what
I'm
> doing wrong now... , may this behavior be influenced by a port specific
> issue? (different OS or something like that?..)
>
> Any ideas?
>
> Thanks
> Diego.
>
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
