Re: An oldie on Blocksize and Performance?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 11 May 1999 21:36:57 +0100
Message-ID: <926455166.3033.1.nnrp-02.9e984b29@news.demon.co.uk>

One correction and one expansion to my first post:

The correction:
DBWn writing does NOT result in 'buffer busy waits', it results in 'write complete waits'. I seem to make this mistake every time someone asks me, and then remember the write (no right) answer 2 days later.

Expansion
I believe the 'grab the latch' takes place as the process starts to hunt down the chain to see if the block is there. The block pin happens if the block is there but a CR copy needs to be made, and then another latch grab probably takes place to attach the copied block to the chain.

If you really mean 'cache buffers chains', this has nothing to

do with table scans.  However, if you have code which does
a large tablescan in parallel, and for each row does an indexed
access into a reference table, then the root block of that index

will be hit extremely frequently by all the PQ slaves - and there will be a lot of contention for that latch (and also possibly for a few other branch blocks in the index).

Select addr, gets, misses, spin_gets from v$latch_children where name = 'cache buffers chains'
and gets > 500000 -- or suitably large number

to see if you have just a couple of chains (hence blocks) causing the problem, then

select dbarfil, dbablk
from x$bh where hladdr = {an addr from above}

to identity which blocks are causing the problem.

P.S. Don't need to air-mail burger and beer, my local pizza place takes order by e-mail.

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

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
Received on Tue May 11 1999 - 15:36:57 CDT