Re: 8.1.7 cache buffer chains contention

Scott,

The more I think about it, the more I am convinced that the problem is actually the 'cache buffers handles' that is the problem, not the 'cache buffers chains'.

It's one of those areas with no documentation and pure guesswork, so here's my theory:

There is a system wide limit of '5 x processes' for the number of db_handles (parameter _db_handles). The 5 may be dictated by _db_handles_cached. Each cursor may also pin up to 32 buffers (set by _db_cursor_buffers_pinned).

If you want to pin a buffer, you need to get the cache buffers chains latch, then create (or acquire) a db_handle - whilst HOLDING the cache buffers chains latch.

If your system is slow to recycle the db_handles because of high usage, then you see misses on the cache buffers handles latch, but these will surely also result in one process holding the cache buffers chains latch longer, thus causing other processes to miss cache buffers chains.

It's all guesswork, since I can't emulate your problem.

However - try increasing

_db_handles_cached (to 10 say)

and seeing if this pushes up

_db_handles

If it doesn't, then increase db_handles
directly, and see if this helps to reduce your wait times.

Remember I asked if you were seeing
fewer gets and more pins - 8.1 has more
clever algorithms to avoid 'getting' blocks if it thinks it is going to re-use them very quickly, so it pins blocks more because a re-use after a pin is cheaper than a get. This may be why you only see the problem in 8.1 and not in 8.0

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

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

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