cbc 诊断脚本-凯发app官方网站

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;

阅读(715) | 评论(0) | 转发(0) |

上一篇：undo 诊断脚本

下一篇：oradebug doc 备查

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们