Hi Ross,
Have you looked at INITRANS on both the tables and indexes yet? Too low
a value will have your SQL lined up waiting for access.
regards
Jerry Gitomer
Ross Mohan wrote in message <7h84fj$4c2_at_dfw-ixnews10.ix.netcom.com>...
>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 Tue May 11 1999 - 08:39:16 CDT
