数据库中latch的CACHE BUFFERS CHAINS的解释

    最近检查系统发现latch free等待比较多，检查V$LATCH_CHILDREN视图发现是CACHE BUFFERS CHAINS最多，对于该事件很久以前网上就有很好的说明了，这里附上官方的说明：

 

Latch: cache buffers chains
Identifier:
Registered In:

Description:
Blocks in the buffer cache are placed on linked lists
(cache buffer chains) which hang off a hash table.
The hash chain that a block is placed on is based on the DBA
and CLASS of the block. Each hash chain is protected by a
single child latch. Processes need to get the relevant latch
to allow them the scan a hash chain for a buffer so that the
linked list does not change underneath them.

Contention: Contention for these latches can be caused by:

- Very long buffer chains.
  There is a known problem that can result in long
  buffer chains -
- very very heavy access to a single block.
  This would require the application to be reviewed.

To identify the heavily accessed buffer chain look at
the latch stats for this latch under
and match this to .
            
                *** IMPORTANT: As of Oracle8i there are many hash buckets
                               to each latch and so there will be lots
                               of buffers under each latch.
                               In 8i the steps below will not help much.

Eg: Given ADDR from V$LATCH_CHILDREN for a heavily contended
    child latch:
select dbafil, dbablk, class, state
   from X$BH where HLADDR='address of latch';

One of these is 'potentially' a hot block in the database.

  
  **Please see How To Identify a Hot Block Within The Database
    to correctly identify this issue

    Once the object/table is found you can reduce the number of blocks requested
    on the particular object/table by redesigning the application or by
    spreading the hits in the buffer cache over different hash chains.
    You can achieve this by implementing PARTITIONING and storing segements of
    the same table/object in different files.
 
  *NOTE*  IF YOU ARE RUNNING 8.1.7:
  
   Please see ALERT: LATCH FREE And FREE_BUFFER_WAITS
                                  Cause Performance Degradation/Hang

后面补充一下具体查找热点块所属对象的方法：

1、执行sql：

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;
找到sleep count较高的地址（ADDR），再执行下面的语句

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 ;

2、由于上面的方法执行起来可能比较慢，而且有时候是针对某个session的事件进行查找的，因此可以用下面的方法：

v$session_wait中找到P1RAW地址，然后执行：

SELECT FILE# , dbablk, class, state ,tch
FROM x$bh WHERE hladdr='' order by tch;

找出touch较高的文件及数据块，最后执行

select * from dba_extents where file_id= and   between block_id and block_id + blocks -1

造成latch free的对象就找到了（其实两种方法是一样的，只不过有些大系统分解了执行更快些，呵呵）。