Cache Buffer LRU Chain Latch

Possible Causes:

•          Processes need to get this latch when they  need to move buffers based on the LRU  block replacement policy in the buffer cache
•          The cache buffer lru chain latch is acquired  in order to introduce a new block into the  buffer cache and when writing a buffer  back to disk, specifically when trying  to  scan the LRU (least recently used) chain  containing all the dirty blocks in the buffer cache.
•          Competition for the cache buffers lru chain latch is symptomatic of intense buffer cache activity caused by inefficient SQL statements. Statements that repeatedly scan large unselective indexes or perform full table scans are the prime culprits. 
•          Heavy contention for this latch is generally due to heavy buffer cache activity which can be caused, for example, by: Repeatedly scanning large unselective Indexes.

Actions:

·         Contention in this latch can be  avoided implementing multiple  buffer pools or increasing the  number of LRU latches with the  parameter _DB_BLOCK_LRU_LATCHES & _db_block_hash_buckets (The default value is generally sufficient for most systems).

·         Its possible to reduce contention for the cache buffer lru chain latch by increasing the size of the buffer cache and thereby reducing the rate at which new blocks are introduced into the buffer cache.

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 Hot Block

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

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;

MOSC 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;