Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: 8.1.7 cache buffer chains contention

Re: 8.1.7 cache buffer chains contention

From: Jonathan Lewis <>
Date: Sat, 8 Sep 2001 14:41:01 +0100
Message-ID: <>


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


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

Host to The Co-Operative Oracle Users' FAQ

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver:
Use spare CPU to assist in cancer research.
Received on Sat Sep 08 2001 - 08:41:01 CDT

Original text of this message