Re: Slow database, too MANY buffers???

Correct.

Hot blocks (the most intensively used blocks) are always likely to leave chains of CR copies, or have CR copies built, but the number 6 will often show up as a fairly strict limit.

The code "tries" to limit the number of CR block to 6 (according to a parameter with a name something like _db_block_max_cr_dba). This code is reasonably aggressive about the limit, but isn't always successful. I can't tell you, though, when exactly Oracle decides to test and enforce the limit.

However, if the buffer cache is excessively large, so that Oracle doesn't have to be so aggressive about finding reusable buffers, it is possible for the numbers to get much larger much more frequently.

I wouldn't necessarily be concerned, for example, by Jared's example of a single block being at 66 copies and all the rest at 6. In this case I would look for problems with latch contention on the child latch covering that block and also see if I had problems with buffer busy waits that could be tracked down to that block.

Only if I found that (say) 20% of my buffer was filled with large numbers of copies of a small number of blocks, would I ask myself if I had a buffer that was too large.

One point I did not make clear, of course: when I said "most intensively used" I was thinking of update activity, not simple query activity.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March
____USA_(FL)_May

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August

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

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 23 January 2003 21:57

>
>Wouldnt this indicate hot blocks? How do I deduce from this, I have a
large
>value for "db_block_buffers"?
>
>Thanks
>Raj
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).