用於排查cache buffers chains

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的優化了。