Rem Filename   : allwait.sql
Rem 
Rem Parameters : None
Rem
Rem Author     : 
Rem RDBMS      : Oracle V8.1.x, V9.x.x
Rem
Rem Modification history
Rem --------------------
Rem 18-Feb-2001 Initial creation.
Rem 16-Apr-2002 Enlarged Raw value column to deal with 64-bit RDBMS implementations.
Rem 09-Oct-2003 - Suppress waits for 'SQL*Net message from client'
Rem             - Display total number of sessions and the number of sessions waiting for
Rem               'SQL*Net message from client'.
Rem             - Improved formatting
Rem 15-Jun-2005 Improved formatting, added header, display background process identification.
Rem
Rem ====================================================================================================================
Rem
Rem Description : Lists the events all session are waiting for.
Rem
Rem               Username   : The username corresponding with the session.
Rem                            In case of a background process, there is no usernames associated with the session,
Rem                            instead a backfround process identification is displayed.
Rem               Sid        : The sid of the waiting session
Rem                    Event : The event the session is waiting for
Rem          Seconds in wait : The nbr of seconds the session is waiting for the event
Rem             Extra Info 1 : Idnetifies what extra information about the event is diqplayed in the value columns.
Rem                    Value : Value of the extra information about the event
Rem                Raw value : Same as value, but expressed as a raw value.
Rem                            Note that the column is 8 positions on 32-bit implementations and
Rem                            16 positions on 64-bit implementations.
Rem
Rem
Rem Sample output
Rem -------------
Rem 
Rem Username       Sid Event                          Extra Info 1          Value Raw value          Seconds in wait
Rem ------------ ----- ------------------------------ --------------- ----------- ---------------- ---------------------
Rem GDIELS          38 SQL*Net message from dblink    driver id       ########### 28444553                383 =  0:06:23
Rem MXXXXXXX        20 enqueue                        name|mode       ########### 54580006              2.013 =  0:33:33
Rem MXXXXXXX        31 SQL*Net message to client      driver id       ########### 54435000                  0 =  0:00:00
Rem STAGING_AREA    18 SQL*Net more data to client    driver id       ########### 54435000                  0 =  0:00:00
Rem                  1 pmon timer                     duration                300 0000012C             21.528 =  5:58:48
Rem                  2 rdbms ipc message              timeout                 300 0000012C                 20 =  0:00:20
Rem                  3 rdbms ipc message              timeout                 300 0000012C                 15 =  0:00:15
Rem                 35 rdbms ipc message              timeout               6.000 00001770                 15 =  0:00:15
Rem                 33 rdbms ipc message              timeout               6.000 00001770                 15 =  0:00:15
Rem                  4 rdbms ipc message              timeout                 300 0000012C                  3 =  0:00:03
Rem                  5 smon timer                     sleep time              300 0000012C              2.983 =  0:49:43
Rem                  6 rdbms ipc message              timeout             180.000 0002BF20             21.467 =  5:57:47
Rem                  9 rdbms ipc message              timeout              30.000 00007530             21.220 =  5:53:40
Rem 
Rem There currently are 40 sessions, of which 27 are waiting for an SQL*Net message from client.
Rem
Rem ====================================================================================================================

SET PAGESIZE  0
SET FEEDBACK  OFF
SET NEWPAGE   NONE
SET VERIFY       OFF
SET LINESIZE 3500

COLUMN UserId    FORMAT A12         HEADING "Username"
COLUMN Sid       FORMAT 9999        HEADING "Sid"
COLUMN Event     FORMAT A30         HEADING "Event"
COLUMN SecWait   FORMAT A21         HEADING "  Seconds in wait"
COLUMN P1Text    FORMAT A15         HEADING "Extra Info 1"
COLUMN P1        FORMAT 99G999G999  HEADING "Value"
COLUMN P1raw     FORMAT A16         HEADING "Raw value"

alter session set nls_numeric_characters = ',.';

@@header_dbinfo 116 DC

SELECT 'There currently are ' || count(*) || ' sessions
  -> ' || sum(decode(event,'SQL*Net message from client',1,0)) || ' session are waiting for a "SQL*Net message from client" event
  -> ' || sum(decode(event,'SQL*Net message from client',0,1)) || ' session are waiting for other events, as listed below'
  FROM v$session_wait;

SET PAGESIZE  9999

SELECT nvl(s.username,'bg: '||bg.name)          "Userid",
       s.sid                                    "Sid",
       sw.event                                 "Event",
       sw.p1text                                "P1Text",
       sw.p1                                    "P1",
       sw.p1raw                                 "P1raw",
       to_char(sw.seconds_in_wait,'9G999G999') || ' =' ||
          to_char(floor(sw.seconds_in_wait/3600),'99') ||':'||
          ltrim(to_char(floor(mod(sw.seconds_in_wait,3600)/60),'09')) ||':'||
          ltrim(to_char(mod(mod(sw.seconds_in_wait,3600),60),'09'))  "SecWait"
  FROM v$session      s,
       v$session_wait sw,
       v$bgprocess    bg
 WHERE s.sid = sw.sid
   AND s.paddr = bg.paddr (+)
   AND sw.event != 'SQL*Net message from client'
 ORDER BY s.username, s.sid;

PROMPT
SET PAGESIZE  9999
SET FEEDBACK  ON
SET NEWPAGE   1