查看异常等待事件
select event,
sum(decode(wait_Time, 0, 0, 1)) "Prev",
sum(decode(wait_Time, 0, 1, 0)) "Curr",
count(*) "Tot"
from gv$session_Wait
where event not like '%SQL*Net%'
and event not like '%timer%'
and event not like 'rdbms ipc message'
and wait_class# != 6
and event not like '%idle wait%'
group by event
order by 4 desc;
找到对应的sid或则sql_id
select s.sid,s.SERIAL#,l.hash_value, l.sql_id, s.event, count(*)
from gv$session s,
gv$sql l
where s.wait_class# != 6
and s.sql_id = l.sql_id
group by s.sid,s.SERIAL#,l.hash_value, s.event, l.sql_id
order by 4 desc, 3 asc;
Select inst_id, username,event,sql_id,count(*) from gv$session where event like 'latch%' group by event,sql_id,inst_id,username order by 5;
select inst_id,sid,serial#,event,status,username,machine,sql_id,prev_sql_id,FINAL_BLOCKING_instance,FINAL_BLOCKING_SESSION,BLOCKING_SESSION,BLOCKING_SESSION_STATUS from gv$session where event like '%cursor: pin S wait on X%';
column event format a30
column sess format a25
set linesize 200
break on id1 skip 1
select decode(request,0,'Holder:',' Waiter:') || s.inst_id || ':' || s.sid||','|| s.serial# sess,
id1, id2, lmode, request, l.type, ctime, s.sql_id, s.event,s.last_call_et
from gv$lock l, gv$session s
where (id1, id2, l.type) in
(select id1, id2, type from gv$lock where request>0
)
and l.sid=s.sid
and l.inst_id=s.inst_id
order by id1, ctime desc, request;
