Re: CR Block copies, always 6 in 10.2.0.3?

From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
To: <ax.mount@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
Date: Mon, 24 Mar 2008 13:46:36 -0000


That limit has been around for many years - and many versions of Oracle -
but it isn't always obeyed (possibly because there are so many scenarios
for visiting a block). However, it does seem to be obeyed a lot of the time.

I don't know how the mechanism really does, but I would assume that the
conditions that could produce extra CRs, or cause waits, would show
waits for the cache buffers chains latch, and buffer busy waits. This is based
on the following hypothetical mechanism:

You have 7 outstanding updates on a single block, with one CURRENT
copy and 5 or 6 CR copies. A session tries to read the block, and has to
grab the CURRENT version and (possibly) make a CR clone. So the
action could be something like:

Grab the relevant latch
Pin the CUR buffer in S mode - find that it needs cloning
Scan the chain for a suitable CR version, decide there isn't one
Change the state on the buffer header for the oldest CR copy
Pin the (now empty) buffer in X mode
Drop the latch
Clone the CUR copy into the (empty) buffer
Rollback the copy to the correct state
Change the buffer to S mode
Supply the data to the end user
Grab  the latch
unpin the two buffers
drop the latch.

If you had too many processes trying to create CR copies at
the same time, then either you have to get more than the
limit normally allows, or you might find processes trying to
pin clones that are currently in X mode - and going into buffer
busy waits as a consequence.

It's all guesswork, of course, and I'm not sure that you could
do much to show whether or not it was roughly what happens.

One thing I do know, though. If you leave all seven updates
in place and uncommitted and starting querying the block
from the same seven sessions, you do a lot more logical I/O
than you might expect, and generate a surprising amount of
redo (relating to block cleanout).
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

----- Original Message -----From: "amonte" <ax.mount@xxxxxxxxx>

To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, March 24, 2008 12:10 AM
Subject: CR Block copies, always 6 in 10.2.0.3?

Hi all

I read from somewhere, I think the wait interface book where it states that
the number of clone copies of data block is governed by _DB_BLOCK_MAX_CR_DBA
which is set to 6. If there will be more than 6 copies then Oracle will wait
for the buffer, this is what I dont get, wait?

So I fired up 7 sessions and see what kind of wait is this! I updated the
same table (EMP from scott tiger), 14 rows in same data block and each
session updates a row, then each session fires a query agaisnt EMP. I
noticed that the number of CR is never more than 6 despite having 7 updates
concurrently and I dont see what is really going on. Does this mean whenever
the 7th CR block is being cloned one of 6 previous CR is being overwritten
or "destroyed"?

I did the test in 10.2.0.3.

TIA

Alex



--------------------------------------------------------------------------------


No virus found in this incoming message.
Checked by AVG.

Version: 7.5.519 / Virus Database: 269.21.8/1339 - Release Date: 22/03/200816:43


--
//www.freelists.org/webpage/oracle-l

References:
- CR Block copies, always 6 in 10.2.0.3?
  - From: amonte

Re: CR Block copies, always 6 in 10.2.0.3?

Other related posts: