Re: An oldie on Blocksize and Performance?

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 - 11:16:10 CDT