1. 首先定位到争用最高的latch， 主要关注点为miss与sleep, sleep次数多，说明争用也越严重。

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.  使用定位到的latch的地址，查询latch保护的extents。

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 ;

其实对于cache buffers chains, 从AWR与ASH报告中，TOP SQL与逻辑读取最高的segments就基本能够确认是哪些对象争用最严重。接下来就是对SQL的优化了。