|
|
select /*+ rule */ vs.inst_id ins,
--vsw.seq#,
chr(39)||vsw.sid||','||vs.serial#||chr(39) "SID/SERIAL#", vp.spid
"OS PID", (case when vsw.event= 'latch free' then 'lf:
'||(select name
from
v$latchname vln
where
vln.latch#=vsw.p2) else vsw.event end) event, sql_id, vs.username username,
-- vs.osuser osuser,
decode(vsw.state, 'WAITED KNOWN TIME','WKT', 'WAITED UNKNOWN TIME','WUT', 'WAITED SHORT TIME','WST', 'WAITING','WTG', vsw.state) wst, decode(vsw.state,'WAITED KNOWN TIME', vsw.wait_time, 'WAITED SHORT TIME', 0, 'WAITING', vsw.seconds_in_wait) "SECS", decode(vsw.event,'enqueue',chr(bitand(vsw.p1,-16777216)/16777215)||
chr(bitand(vsw.p1,16711680)/65535)||' ENQUEUE',vsw.p1text||'='||to_char(vsw.p1)) p1_text, vsw.p2text||'='||to_char(vsw.p2)||' '||vsw.p3text||'='||to_char(vsw.p3)
p2_p3_text, decode(command,2,'INS',3,'SEL', 6,'UPD', 7,'DEL',47,'PLSQ',189,'MERG',170,'CLMD',85,'TRUNC', command) "DML", DECODE(status ,'KILLED','K','ACTIVE','A','INACTIVE','I') SessStat
--,
-- ( select decode(DEGREE,0,' ','Y-'||degree) from gV$PX_SESSION px where vs.inst_id = px.inst_id and vs.sid = px.sid) "Prll"
from
gv$session_wait vsw, gv$session vs, gv$process vp
where
vs.inst_id = vsw.inst_id and vs.inst_id = vp.inst_id and vs.sid=vsw.sid
and
vp.addr = vs.paddr and vs.username is not null and vsw.wait_class != 'Idle'
--- and vsw.event not like '%Stream%'
---and vsw.event not like 'SQL*Net%'
---and vsw.event not like '%pipe%'
---and vs.event# not in ( 1,224,229,312, 313,67, 320,327,328,329,330,331,332,333,334,335,
---281, 333,334,327, 268,335,60,64,6, 328, 230,341, 267,386,387,845,846,847,379,73)
---and vsw.event != 'virtual circuit status'
---and vs.username != 'DBA_MON'
---and vs.username != 'ADBA_PERFMON'
---and vs.username != 'SYSTEM'
order by vs.inst_id,
vs.status, decode(vsw.state, 'WAITING', 1, 'WAITED KNOWN TIME', 2, 'WAITED SHORT TIME', 3, 'WAITED UNKNOWN TIME', 4, 5), decode(vsw.state, 'WAITING', vsw.seconds_in_wait,
vsw.wait_time)
|
No comments:
Post a Comment