Latch: cache buffers handles

Oracle: latch: cache buffers handles

From Anjo Kolk:

There are two groups of buffer handles: there is a private group (_db_handles_cached) and a global group (db_handles). Each session/process has an initital number of db_handles (5) (not latch protected) and if you need more, you will get it from the global pool (latch protected). So you see contention on the global pool.

Any operation on a buffer means that you will get a buffer handle. If you work with large joins it you will probably need many concurrent buffer handles. Increasing the number of handles per session/process will reduce the amount of latching (assuming that there is no bug).

From Tanel Poder:

Buffer handle (X$KCBBF) is the linking state object between buffer header (X$BH) and the session/call state object. I haven't checked how the "consistent gets - examination" behaves but otherwise all logical IOs need to allocate a buffer handle for the buffer get.

(_db_handles_cached helps here - when a buffer is pinned, it's not pinned immediately after use and the handle will remain pointing to relevant buffer header as well. That's the mechanism which allows you to see cheper LIOs - the "buffer is already pinned" ones).

When a session grabs / releases buffer handles from the buffer handle pool (total sie controlled by _db_handles parameter), the cache buffer handles latch must be taken. The _db_handles_cached defaults to 5 which states how many buffer handles a session keeps to itself during its lifetime, so there's no need to constantly allocate/deallocate them in the beginning/end of a call.

But there's one more parameter, _cursor_db_buffers_pinned which allows a session to grab many more db handles for duration of a db call - and release them in the end of the call. I don't know exact algorithms behind that, but you should be able to reproduce it with a nested loop join of lots of tables with index range scans for example. The issue here is that when the db call finishes then these extra allocated handles have to be released back to global pool (which Anjo mentioned)

other references

http://esemrick.blogspot.com/2006/04/getting-handle-on-logical-io.html

http://www.jlcomp.demon.co.uk/buffer_handles.html

What would ADDM do?

just points to the statement but provides no solution