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

COLUMN INST      FORMAT 9           HEADING "I"
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 s.inst_id INST,
       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 gv$session      s,
       gv$session_wait sw,
       gv$bgprocess    bg
 WHERE s.sid = sw.sid
   AND s.inst_id = sw.inst_id
   AND s.inst_id = bg.inst_id   
   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