cbc 诊断脚本

当出现latch : cache buffer chain 等待时需要确定在哪里出现热点，当然对非常忙的环境，执行以下语句会加重负载

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 like '%buffer cache chain%' 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 ;

select sid,event,p1raw,p2raw from v$session where event='latch: cache buffers chains' order by event;

SELECT File#, dbablk, class, state FROM x$bh WHERE hladdr='&P1RAW';

with bh_lc as
      (select /*+ ORDERED */
         lc.addr, lc.child#, lc.gets, lc.misses, lc.immediate_gets,
         lc.immediate_misses, lc.spin_gets, lc.sleeps,
         bh.hladdr, bh.tch tch, bh.file#, bh.dbablk, bh.class,
         bh.state, bh.obj, s.sql_hash_value
       from
         x$kslld ld,v$session_wait sw, v$latch_children lc, v$session s, x$bh bh
       where lc.addr =sw.p1raw
     and sw.sid=s.sid
         and sw.p2= ld.indx
         and ld.kslldnam='cache buffers chains'
         and lower(sw.event) like '%latch%'
         and sw.state='WAITING'
         and bh.hladdr=lc.addr)
select bh_lc.hladdr, bh_lc.sql_hash_value, bh_lc.tch,
o.owner, o.object_name, o.object_type,
           bh_lc.child#, bh_lc.gets, bh_lc.misses, bh_lc.immediate_gets,
           bh_lc.immediate_misses, bh_lc.spin_gets, bh_lc.sleeps
    from bh_lc, dba_objects o
    where bh_lc.obj = o.object_id(+)
  union
select bh_lc.hladdr, bh_lc.sql_hash_value,bh_lc.tch,
o.owner, o.object_name, o.object_type,
           bh_lc.child#, bh_lc.gets, bh_lc.misses, bh_lc.immediate_gets,
           bh_lc.immediate_misses, bh_lc.spin_gets, bh_lc.sleeps
    from bh_lc, dba_objects o
    where bh_lc.obj = o.data_object_id(+)
 order by 1,2,3 desc;