Re: An oldie on Blocksize and Performance?

The tickler about v$latch_children is an excellent point, worthy of the B & B. I had thought there was only one CBC latch, and so the kids don't make any
sense to me....Could you say a bit more about the children in this case?

Hash buckets r us, so I don't think I've gotten to the core of the problem yet. Sad truth is, i don't yet have a feel for the multiple buffer pool in 8 yet, and suspect i am flushing madly in the first 64K of the CBC due to wild full table scans....but, this is a guess...i need, somehow
to "instrument" my buffer cache to nail this one down....

Sorry, JL, but plane fare's your worry! :) I won't order your Newcastle or Smithick's
until you land.

Ross

Jonathan Lewis wrote in message
<926266708.21134.0.nnrp-03.9e984b29_at_news.demon.co.uk>...
>I won't claim to be up to scratch on this but ...
>
>>1) event "buffer busy waits" only occurs when I want to *write* and block
>>that is already in XCUR mode, or being flushed to disk. True or False?
>
>Causes - you want exclusive access, and another process has the block
>pinned. 3 causes - another process is changing the block - DBWn has
>claimed it to write it out - it is the CUR block and another process has
>grabbed it to create its own CR version.
>
>>2) The cache buffer chains latch will be grabbed when cloning a block.
True
>> or False?
>
>I'd say this was true (I assume by cloning you mean creating a CR version).
>Both
>the CR version and all other versions hash to the same value, so something
>has to protect the chain as a new entry it made. See also (1). You might
>like
>to look at the x$bh to find out how many blocks have multiple copies and
>which blocks they are. There is a script on my Website that may help.
>There is also a parameter (can't remember the name, something about
>cr_copies
>which limits the number of consistent read copies of a block that are
>allowed.
>In extreme cases this may help to reduce contention a bit).
>
>
>>3) upping freelists will only reduce INSERT contention. True or False?
>
>True - although a hair-splitting fanatic might point out that an update
that
>causes row migration would require access to the free list, and so ....
but
>in general that would be more misleading than helpful.
>
>>4) There is no direct tuneable for the cache buffer chains latch. True or
>>False?
>
>Yes and No. if you increase the number of cache buffer chains you reduce
>the probability of contention for any given chain latch. The relevant
>parameter
>is _db_block_hash_buckets, which I think defaults the the smallest prime
>number
>greater than 25% of the db_block_buffers. You might find it useful to look
>at the
>stats on v$latch_children to see if ALL your cache buffer chains latch are
>subject
>to contention, or whether it is just a very small number of them before you
>worry
>about fixing this one up though. See also (2) about controlling
>consistent_read copies.
>
>
>Do I qualify for the burger and brew ? And you did mean Washington in
>the Tyne and Wear region of the UK didn't you ?
>
>--
>
>Jonathan Lewis
>Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
>
>
Received on Sun May 09 1999 - 22:44:49 CDT