Thursday, June 24, 2021

sess_details

col SID/SERIAL# for a15
col EVENT for a30
col P1_TEXT for a20

col username for a10
col P2_P3_TEXT for a23

col wst for a7
col
"OS PID" for a10
col dml  for a7


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