Re: Multiple copies of the same block in the buffer cache

Bug 1967363 is the one I was thinking of, but there is also

Bug:2079526 - FREE BUFFER WAITS DURING CHECKPOINT

   If the number of dirty buffers that need to be written to disk is    larger than the number that dbwr can write per batch, this bug may    cause degradation in database performance. The wait events in    V$session_wait or statspack will show lots of waits on 'latch free'    (for "cache buffers chain" latches) and/or waits on 'free buffer waits'.

This is not fixed in 8.1.7.3.

It is also possible that since your index is a cluster index perhaps there is still some remnant of 1967363 lurking.

>Curiously the touch count is fairly low, often 0.

Touch count is never incremented more than once every three seconds - however, 0 is rather low, so perhaps this is indicative of the 2079526 bug more than the 1967363 bug.

>I have already increased _db_block_hash_latches to a large value. I
>tried to increase it to the same as db_block_buffers, but whenever
>I set it past some (unknown) threshold, it jumped straight to 2^32.

Under 8.1, the latch count is supposed to be quite small compared to the number of buckets - and the bucket count is about twice the block count. The 2^32 is an interesting oddity, but perhaps your better bet is to fiddle with the _db_block_hash_bucket a bit. I suspect that your analysis that pins things to a block is good though.

>I see the top latch sleeps are all on extent# 0, block# 2 of various
>index segments.

How big is the problem in absolute time ? How many different indexes are involved, and how many of them are cluster indexes ?

>Is there much more that can be done, or am I just stuck wishing I was
>using 9i on a system with shared cache buffers chains latches?
>

Too bad, HP doesn't support shared latches even when you get there. Can you run a simple test to see if the latch problem still persists on you version:

create table t1 (n1 number, n2 number);
insert into t1 select rownum , 100 from all_objects;

create table t2 (n1 number primary key, v1 varchar2(10)); insert into t2 select rownum , 'xxxxx' from all_objects;

commit;

select /*+ ordered use_nl(t2) */

    count(*)
from

    t1, t2
where

    t2.n1 = t1.n2
;

Allowing for typing error, this should
scan T1, and hit the index on t2 a few
thousand times. Take a snapshot of
latch behviour before and after, and see if the latch on the root block get hit a few times, or 2 x number of rows in T1.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases