Fix high "cache buffer chain" waits

from:http://www.dba-oracle.com/t_high_cache_buffer_chain_waits_contention.htm

 

 

Question: We have just upgraded our systems to run Oracle Apps 11.5.10.2 on Solaris 64 bit on Oracle 10.2.0.2.

We have a 9 gig SGA and we're having problems 80/20 and 50/20 "cache buffer chain" waits running 3 concurrent payroll schemes.

 

How to I reduce these "cache buffer chain" latch waits?
 

Answer: The "cache buffer chain" latch wait is normal, but high values are associated with high simultaneous buffer access, similar to a freelist shortage on an index or table segment header.

 

MetaLink also suggests using the _db_block_hash_buckets and
_db_block_hash_latches undocumented parameters have been suggested as a remedy, but always check with MetaLink before using any undocumented parameters.  To see the "cache buffer chain" waits:

select 
   count(*)    child_count,
   sum(gets)   sum_gets,
   sum(misses) sum_misses,
   sum(sleeps) sum_sleeps
from 
   v$latch_children 
where 
   name = 'cache buffers chains';

 

Finding the hot blocks

 

To see the exact blocks that experience the "cache buffer chain" waits, you start byfinding the object associated with the data block and then see if it is a segment header block.  For example, start with this query:

select 
   P1 
from 
   v$session_wait 
where 
   event = 'cache buffer chains';

 

Next, use these directions for using P1 to find the exact data block.

 

If you are using Automatic Segment Storage Management (ASSM), you can seesegment header contention under high DML loads, and you may need to redefine your freelists back to the traditional freelist structures.

 

If you are not using ASSM (bitmap freelists), you can easily relieve the buffer chain latch wait by adding freelists, up to your high-water mark of concurrent DML on the object:

 

alter index hot_idx freelists 4;

 

Metalink has this script to locate a hot block:

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;
 

Also, check out if you have undo latch contention.  The undo segment extension Oracle metric occurs when the undo segment extension is being extended or shrunk. The session must wait until the operation on the undo segment has finished. 
 

select

   swh.seq#,

   sess.sid,

   sess.username username,

   swh.event     event,

   swh.p1,

   swh.p2

from

   v$session               sess,

   v$session_wait_history  swh

where

   sess.sid = 74

and

   sess.sid = swh.sid

order by

   swh.seq#;

 

 

SEQ# SID USERNAME EVENT                       P1         P2

---- --- -------- --------------------------- ---------- ----------

   1  74 PCS      buffer busy waits                    3      21277

   2  74 PCS      latch: cache buffers chains 1556332118        172

   3  74 PCS      latch: cache buffers chains 1556332118        172

   4  74 PCS      buffer busy waits                    4        155

Take the p1raw column from the v$session_wait for the waiting session.

In sum, most buffer cache waits can be fixed with additional freelists, but there are exceptions.  For a full treatment of fixing latch contention, see my book "Oracle Tuning: The Definitive Reference",