Re: CR Block copies, always 6 in 10.2.0.3?

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_at_gmail.com> To: "oracle-l" <oracle-l_at_freelists.org> 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/2008 16:43

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