What is a buffer handle ?
(Recreated from an original written for the Dizwell Wiki).
A lot of people spend time looking at logical I/O when assessing the performance of a system, and take the logical I/O as a rough indication of CPU consumption. This is not unreasonable, but may be a little misleading for various reasons.
One of the more significant errors in this approximation appears because Oracle can visit a data buffer without doing a logical I/O.
Of course, before you can appreciate the significance of this, you have to know what a logical I/O actually is. There are two possible definitions - either it is a call to one of the 802 subroutines listed in x$kcbwh, or it is a visit to a buffer that requires at least one get on a cache buffers chains latch. (The 802 is from 10.2.0.1 - it used to be about 450 in 8i).
So how can it be safe to visit a buffered block without first using a latch to protect it - the answer is that you have to anticipate using it several times, so you use latching to acquire it the first time and latching to release it when you have finished with it, but pin it in the interim so that you can visit it several times without having to go through the CPU intensive process of competing for latches.
The object used to pin a buffer (technically it’s the buffer header, not a buffer itself) is called a buffer handle.
The total number of available buffer handles is set at instance start-up, and is (probably) dependent on the number of processes - apparently 5 * processes, although that value five may be related to the fact that the default value for _db_handles_cached is five. These handles appear as the structure x$kcbbf.
In order to pin a buffer header a session must first acquire a buffer handle, and the first step to doing this is to grab the cache buffer handles latch to protect the integrity of the x$kcbbf array. Of course if the session had to grab this latch every time it wanted to pin a buffer the latch would become a major point of contention - so each session is allowed to build a little “reserved set” or cache of handles. The limit on the number of reserved handles that a session can mark is 5 - set by the parameter _db_handles_cached; a session does not need to get the latch to use, and re-use, this small set of handles.
Of course, some queries may be quite complex, and operate most efficiently if a larger number of buffers can be pinned - so sessions are allowed to acquire more than just the limit of five, if there are free handles available. To limit a session, though, Oracle does some arithmetic that seems to be working on the basis of allowing each process to pin a “fair share” of the buffer - so the total number of handles a session is allowed to hold temporarily is (approximately) db_block_buffers / processes. (This raises some interesting questions for dynamic cache re-sizing from 9i onwards). This limit is published as parameter _cursor_db_buffers_pinned.
The cost and effectiveness of buffer pinning can be seen in two statistics, buffer is pinned count which reports the number of times you have visited a buffer without the expense of having to use a latch; and no buffer to keep pinned count which should have been called “no pin available for pinning I buffer I want count” and tells you when you wanted to pin a buffer, but had either exceeded your limit, or could not find a free pin in the array.
The first statistic is a measure of work that your session had done. The second statistic is a hint that perhaps you have a value of processes that is too high for the size of your buffer or, conversely, a value of db_block_buffers that is too small for the number of processes you want to run.
Buffers can only be pinned for the duration of a database call - the pins have to be released when the call ends.
A buffer cannot be flushed from memory if it is pinned - even if it appears to be at the tail end of the LRU list.
Buffers (technically buffer headers) can be pinned in exclusive mode or shared mode. If you pin a buffer in exclusive mode (which sets the mode_held in x$bh to the value 2) then other sessions that want to pin the buffer will attach their pins to a “waiters list” on the buffer header, and go into a “buffer busy waits” state. [Corrected Sept 2008 – originally said “state” rather than “mode_held”. Error spotted by Qihua Wu (Daniel)): I believe 2 is for exclusive, and 1 is for shared.]
Most buffer gets required two latch acquisitions - one to locate and pin the buffer, one to unpin the buffer; but some gets (reported by statistic consistent gets - examination) need only one latch acquisition and view the buffer whilst holding the latch. Examples of this are: reading the root block of an index, reading an undo block while creating a consistent read data block, reading a block in a single table hash cluster - unless it is found to have the ‘collision flag’ set.
Latch acquisition is so CPU-intensive, that the Oracle kernel has evolved over time to increase the number of code points where buffers can be pinned. The buffers most commonly pinned are probably the leaf blocks of indexes used in range scans; but I believe that undo blocks (not the undo segment header blocks) are also pinned for the duration of a DML statement (or until full), as are branch blocks of indexes on the inner tables of nested loop joins, and branch blocks used in index skip scans.