The latch: cache buffers chains Oracle metric is used to protect a buffer list in the buffer cache. These latches are used when searching for, adding, or removing a buffer from the buffer cache. Contention on this latch usually means that there is a block that is greatly contended for (known as a hot block).
Recommendation:
To see the exact blocks that
experience the "cache buffer chain"
waits, you start by finding the
object associated with the data
block and then see if it is a
segment header block. This is a
situation that must be caught in the
act.
SELECT p1 "file#", p2 "block#", p3
"class#"
FROM v$session_wait
WHERE event = 'cache buffer chains';
Once a
common block has been identified use
this query to identify the hot
object:
SELECT relative_fno, owner,
segment_name, segment_type
FROM
dba_extents
WHERE file_id = &file
AND &block BETWEEN block_id AND
block_id + blocks - 1;
The next step is tuning queries that interact with this object. Secondly increasing PCTFREE and allowing for less dense blocks may address the problem, depending on the real root cause, however the best solution is SQL tuning.
