--
-- Author: Ahbaid Gaffoor
-- Date: Tuesday 25th February 2003
-- File: waittasks.sql
--
-- Purpose: Display currently waiting tasks
--
-- License: You may reuse this software at will provided you quote and retain this header
--
-- Disclaimer: No warranty is provided with this software, no liability or guarantees
-- are given in any form
--
--
set lines 200
set pages 100
clear breaks
clear columns
col c1 heading 'SID' format 999
col c2 heading 'User Name' format a16 wrap
col c3 heading 'Event Name' format a10 wrap
col c4 heading 'InWait(s)' format 999,999.99
col c5 heading 'Wait Time' format 999,999.99
col c6 heading 'state' format a10 wrap
col c7 heading 'P1 text' format a10 wrap
col c8 heading 'P1 Val' format 999,999,999
col c9 heading 'P1 Raw' format a10
col c10 heading 'P2 Text' format a10 wrap
col c11 heading 'P2 Val' format 999,999,999
col c12 heading 'P2 Raw' format a10
col c13 heading 'P3 Text' format a10 wrap
col c14 heading 'P3 Val' format 999,999,999
col c15 heading 'P3 Raw' format a10
SELECT
a.sid c1,
decode(b.username, NULL,c.name, b.username) c2,
a.event c3,
a.seconds_in_wait c4,
a.wait_time c5,
a.state c6,
a.p1text c7,
a.p1 c8,
a.p2text c10,
a.p2 c11,
a.p3text c13,
a.p3 c14
FROM
v$session_wait a,
v$session b,
v$bgprocess c
where
event not like 'DFS%' and
event not like 'KXFX%' and
a.sid = b.sid and
b.paddr = c.paddr (+) and
event not in
('lock element cleanup',
'pmon timer',
'rdbms ipc message',
'smon timer',
'SQL*Net message from client',
'SQL*Net break/reset to client',
'SQL*Net message to client',
'SQL*Net more data to client',
'dispatcher timer',
'Null event',
'parallel query dequeue wait',
'parallel query idle wait - Slaves',
'pipe get',
'PL/SQL lock timer',
'slave wait',
'virtual circuit status',
'WMON goes to sleep'
)
order by 4 desc ;
| 
