Re: 8.1.7 cache buffer chains contention

I've had a couple of thoughts about your latching problem, and changes to the way Oracle may be working.

1st thought is the index root block that you've identified also an index leaf block ? If so, then Oracle may pin the block whilst scanning the the table blocks - but this is a recent change to the code. It may be that the action of pinning and unpinning the block takes an amount of time proportional to the number of processes currently on the pin-list - and in your case you have lots of concurrent processes.

2nd thought - how many versions of the block appear on the hash-chain ? There is supposed to be a limit of 5 or 6. If there are too many, then the time taken holding the latch whilst scanning for the correct version of the buffer will go up. On the other hand, if there are too few then the concurrent processes may keep on flushing each other's CR images and having to rebuild them thus increasing the number of times buffers have to be pinned for copying.

--
Jonathan Lewis

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
See http://www.jlcomp.demon.co.uk/book_rev.html

For latest news of public appearances
See http://www.jlcomp.demon.co.uk

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.