Oracle session waits script
set lines 2000 pages 100
Col clntpid format a8 heading 'Client|PID' justify center
Col cnctm format a11 heading 'Connection|Time'
Col event format a50 heading 'Event' justify center truncate
Col hrcnct format 999.99 heading 'Hours|Cnct'
Col hridl format 999.99 heading 'Hours| Idle'
col inst format 9 heading 'I'
Col mach format a12 heading 'Machine' justify center truncate
Col minutes format 999.00 heading 'Minutes|Waited' justify center
Col ospid format a7 heading 'Server|PID' justify center
Col osuser format a12 heading 'OS Id' justify center
Col pgrm format a15 heading 'Program' justify center truncate
Col sess format a10 heading 'SID|Serial#' justify center
col stat format a1 heading 'S|T'
Col state format a18 heading 'State' justify center truncate
Col uname format a22 heading 'Oracle|Id' justify center
col bsess format 999999 heading 'Block|sess' justify center
col binst format 99 heading 'Block|inst' justify center
break on inst skip 1
select s.inst_id inst
, s.username uname, s.sid||','|| s.serial# sess
, s.osuser osuser
, p.spid ospid
, s.sql_id sql_id
-- , s.machine mach
-- , s.process clntpid
-- , s.program pgrm
, round(s.last_call_et / 3600, 2) hridl
, w.seconds_in_wait / 60 minutes
, s.blocking_session bsess
, s.blocking_instance binst
, decode(status, 'ACTIVE', '*'
, 'INACTIVE', ''
, 'KILLED', 'X') stat
, w.event||' ('||w.p1||':'||w.p2||':'||w.p3||')' event
from gv$session s
,gv$process p
,gv$session_wait w
where s.type != 'BACKGROUND'
and s.paddr = p.addr
and w.sid = s.sid
and s.inst_id = p.inst_id
and s.inst_id = w.inst_id
and s.username is not null
and w.event not like 'SQL*Net message%'
order by inst, hridl desc,minutes desc
/
No Responses