Cache Buffer Chains

Block headers are placed on the buffer cache in linked lists (cache buffer chains) which are accessed through a hash table. One or more hash chains are protected by one child of this latch. Processes need to get the child latch to scan for a buffer. This prevents the linked list from changing while scanning.

'latch: cache buffers chains' contention is typically encountered because SQL statements read more buffers than they need to and multiple sessions are waiting to read the same block.

• Contention on the cache buffers chains latch may occur when block headers are accessed very frequently (via logical reads). These are often referred to as Hot blocks. Look for SQLs that access the blocks in question and determine if the repeated reads are necessary. This may be within a single session or across multiple sessions. In order to identify the contending SQLs:
• Check ASH data to find the SQLs most often waited for this latch.
• Check SQL ordered by logical reads to find out the top SQLs performing extensive logical reads.
• Check Segments by logical reads to find the segments upon which most of the logical reads occurred.

• When execution plans are optimal, then further checks are needed from your application side to validate whether the concurrency on blocks imposed via repeated execution of sqls are justified.
• If you have assigned a very large buffer cache, then reducing the size may help, in very rare case.
• Index leaf blocks may see contention due to key values that are increasing steadily (using a sequence) and concentrated in a leaf block on the "right-hand side" of the index. Look at using reverse key indexes if range scans aren't commonly used against the segment (remember that this change means that range scan won’t be used for this index).
• Use hash partitioning to spread values across blocks.
• Rebuilding table with more PCTFREE may distribute rows in multiple blocks. This will reduce the number of rows per block and hopefully, spread out contention for the blocks (at the expense of wasting space). This may cause some queries to run slower because they will need to access more blocks to obtain the same number of rows. 

1.

SELECT child# "cCHILD" ,
  addr "sADDR" ,
  gets "sGETS" ,
  misses "sMISSES" ,
  sleeps "sSLEEPS"
FROM v$latch_children
WHERE name = 'cache buffers chains'
ORDER BY 5,  1,  2,  3;

2. 

SQL>  column segment_name format a35

SELECT  /*+ RULE */
  e.owner  || '.'  || e.segment_name segment_name,
  e.extent_id extent#,
  x.dbablk - e.block_id + 1 block#,
  x.tch,
  l.child#
FROM sys.v$latch_children l,
  sys.x$bh x,
  sys.dba_extents e
WHERE x.hladdr = '&ADDR'
AND e.file_id  = x.file#
AND x.hladdr   = l.addr
AND x.dbablk BETWEEN e.block_id AND e.block_id + e.blocks -1
ORDER BY x.tch DESC ;

3.

WITH bh_lc AS
  (SELECT
    /*+ ORDERED */
    lc.addr,
    lc.child#,
    lc.gets,
    lc.misses,
    lc.immediate_gets,
    lc.immediate_misses,
    lc.spin_gets,
    lc.sleeps,
    bh.hladdr,
    bh.tch tch,
    bh.file#,
    bh.dbablk,
    bh.class,
    bh.state,
    bh.obj
  FROM x$kslld ld,
    v$session_wait sw,
    v$latch_children lc,
    x$bh bh
  WHERE lc.addr  =sw.p1raw
  AND sw.p2      = ld.indx
  AND ld.kslldnam='cache buffers chains'
  AND lower(sw.event) LIKE '%latch%'
  AND sw.state ='WAITING'
  AND bh.hladdr=lc.addr
  )
SELECT bh_lc.hladdr,
  bh_lc.tch,
  o.owner,
  o.object_name,
  o.object_type,
  bh_lc.child#,
  bh_lc.gets,
  bh_lc.misses,
  bh_lc.immediate_gets,
  bh_lc.immediate_misses,
  spin_gets,
  sleeps
FROM bh_lc,
  dba_objects o
WHERE bh_lc.obj = o.object_id(+)
UNION
SELECT bh_lc.hladdr,
  bh_lc.tch,
  o.owner,
  o.object_name,
  o.object_type,
  bh_lc.child#,
  bh_lc.gets,
  bh_lc.misses,
  bh_lc.immediate_gets,
  bh_lc.immediate_misses,
  spin_gets,
  sleeps
FROM bh_lc,
  dba_objects o
WHERE bh_lc.obj = o.data_object_id(+)
ORDER BY 1,2 DESC;

To find the various latches currently having high wait times, execute the following query to get the latch details:






SELECT inst_id              INT,
       latch#,
       level#,
       name,
       gets,
       misses,
       gets / misses        RATIO,
       sleeps,
       spin_gets,
       immediate_gets       Igets,
       immediate_misses     Imisses,
       wait_time / 1000000 "WT"
FROM   gv$latch
WHERE  misses > 1000
ORDER  BY inst_id,
          spin_gets DESC;