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

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
>
>The root index block problem is more likely to be
>the bug fixed in 8.1.7.3 and 9.0.1.3 - the root block
>doesn't change much, so shouldn't get many
>read consistent copies ever. On trailing blocks on
>leaf indexes, it might help - but you have to be careful
>that you don't cause the problem to occur 'from the opposite
>direction' namely that there are too few CR copies, so
>processes keep having to clone a recent block and roll
>it back a bit.

Bug 1967363 ? I'm using 8.1.7.3 (on HP-UX 11i 64-bit)

OK, I understand the comment about no CR blocks, the times I've seen that have been when inserting into an initially empty index cluster. In this case, I don't think there are CR copies, and it's just straight root-block contention.

Overall the problem is top wait event in this instance is latch wait.

I ran a query from Metalink Doc Id 163424.1 to identify hot blocks.

I see the top latch sleeps are all on extent# 0, block# 2 of various index segments. Doing block dumps shows that these are the first branch blocks in the extent. Is there any reason not to assume that this is the root block?

Curiously the touch count is fairly low, often 0.

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. _spin_count is up at 5000, the OS shouldn't be descheduling processes holding latches, since I'm using hpux_sched_noage=178.

The system is the back-end for a batch processing environment, with dozens of concurrent processes all doing pretty much the same thing.

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?

-- 
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/