How to Solve Latch- cache buffers chains

首先找到产生cache buffers chains等待最多的latch地址

 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;

然后根据找到的latch地址获取segment的名字

 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 ;

上面查询的tch字段可以作为衡量数据块hot程度的参考

可以从以下角度着手解决这个问题：

• 查找是否有效率差的DML或者select语句，造成需要访问相同的block。（Note:1342917.1 Troubleshooting 'latch: cache buffers chains' Wait Contention）
• 减小buffer cache - 只在很少的情况下有用
• 配置多个DBWR。（Note:62172.1 - Understanding and Tuning Buffer Cache and DBWR）
• 增加table的PCTFREE存储参数，可以使单个数据块中包含更少的行
• 考虑使用reverse key index

• 相关bug

  下面附送两张图，帮助理解cache buffers chains的结构：
  hash chain list:
  

hash bucket & latch:

参考文章：
http://www.askmaclean.com/archives/oracle%E9%97%A9latch-cache-buffers-chains.html
http://blog.csdn.net/tianlesoftware/article/details/6573438