Re: An oldie on Blocksize and Performance?

What is happening is that i am getting killed on contention for the CBC latch.
I suspect it is due to massive full table scanning. I have tried to ameliorate
by increasing db_block_hash_buckets, and upping the spin_count ( whatever happened to latch_spin_count in 8.0.5? ) with noticeable but still unsatisfactory
improvements.

The "expert" before me made the db in 16K blocks. I want to know whether i should burn cycles redoing it to ( it's for the sake of argument, a relatively
pure OLTP instance ) 4K or 8K or whether I should attempt to reduce full table scanning.....or whether I should do something awfully clever with the multiple buffer pools...

other wait events.....an occasional buffer busy wait, and a fair bunch of db_file_sequential/scattered_read events. more of the former than latter. But the latch wait time is far and away more than the wait times for these other guys.....

I would airmail a beer and burger to England for this one.......

TIA 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 Mon May 10 1999 - 21:37:10 CDT