text: -- +----------------------------------------------------------------------------+
  -- |                          Jeffrey M. Hunter                                 |
  -- |                      jhunter@idevelopment.info                             |
  -- |                         www.idevelopment.info                              |
  -- |----------------------------------------------------------------------------|
  -- |      Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved.       |
  -- |----------------------------------------------------------------------------|
  -- | DATABASE : Oracle                                                          |
  -- | FILE     : sess_waiting.sql                                                |
  -- | CLASS    : Session Management                                              |
  -- | PURPOSE  : This script produces a report of the top sessions that have     |
  -- |            waited (the entries at top have waited the longest) for         |
  -- |            non-idle wait events (event column). The Oracle Server          |
  -- |            Reference Manual can be used to further diagnose the wait event |
  -- |            (along with its parameters). Metalink can also be used by       |
  -- |            supplying the event name in the search bar.                     |
  -- |                                                                            |
  -- |            The INST_ID column shows the instance where the session resides |
  -- |            and the SID is the unique identifier for the session            |
  -- |            (gv$session). The p1, p2, and p3 columns will show event        |
  -- |            specific information that may be important to debug the         |
  -- |            problem.                                                        |
  -- | EXAMPLE  : For example, you can search Metalink by supplying the event     |
  -- | METALINK : name (surrounded by single quotes) as in the following example: |
  -- | SEARCH   :                                                                 |
  -- |                          [ 'Sync ASM rebalance' ]                          |
  -- | NOTE     : As with any code, ensure to test this script in a development   |
  -- |            environment before attempting to run it in production.          |
  -- +----------------------------------------------------------------------------+
  
  SET TERMOUT OFF;
  COLUMN current_instance NEW_VALUE current_instance NOPRINT;
  SELECT rpad(instance_name, 17) current_instance FROM v$instance;
  SET TERMOUT ON;
  
  PROMPT 
  PROMPT +------------------------------------------------------------------------+
  PROMPT | Report   : Session Waits                                               |
  PROMPT | Instance : &current_instance                                           |
  PROMPT +------------------------------------------------------------------------+
  
  SET ECHO        OFF
  SET FEEDBACK    6
  SET HEADING     ON
  SET LINESIZE    180
  SET PAGESIZE    50000
  SET TERMOUT     ON
  SET TIMING      OFF
  SET TRIMOUT     ON
  SET TRIMSPOOL   ON
  SET VERIFY      OFF
  
  CLEAR COLUMNS
  CLEAR BREAKS
  CLEAR COMPUTES
  
  COLUMN instance_name     FORMAT a9            HEADING 'Instance'
  COLUMN sid               FORMAT 999999        HEADING 'SID'
  COLUMN serial_id         FORMAT 99999999      HEADING 'Serial ID'
  COLUMN session_status    FORMAT a9            HEADING 'Status'
  COLUMN oracle_username   FORMAT a20           HEADING 'Oracle User'
  COLUMN state             FORMAT a8            HEADING 'State'
  COLUMN event             FORMAT a25           HEADING 'Event'
  COLUMN wait_time_sec     FORMAT 999,999,999   HEADING 'Wait Time (sec)'
  COLUMN last_sql          FORMAT a45           HEADING 'Last SQL'
  
  SELECT
      i.instance_name                 instance_name
    , s.sid                           sid
    , s.serial#                       serial_id
    , s.username                      oracle_username
    , sw.state                        state
    , sw.event                        event
    , sw.seconds_in_wait              wait_time_sec
    , sa.sql_text                     last_sql
  FROM
      gv$session_wait sw
    , gv$session s
    , gv$sqlarea sa
    , gv$instance i
  WHERE
        sw.event NOT IN (   'rdbms ipc message'
                          , 'smon timer'
                          , 'pmon timer'
                          , 'SQL*Net message from client'
                          , 'lock manager wait for remote message'
                          , 'ges remote message'
                          , 'gcs remote message'
                          , 'gcs for action'
                          , 'client message'
                          , 'pipe get'
                          , 'null event'
                          , 'PX Idle Wait'
                          , 'single-task message'
                          , 'PX Deq: Execution Msg'
                          , 'KXFQ: kxfqdeq - normal deqeue'
                          , 'listen endpoint status'
                          , 'slave wait'
                          , 'wakeup time manager'
                        )
    AND sw.seconds_in_wait > 0 
    AND sw.inst_id = s.inst_id
    AND sw.sid = s.sid
    AND s.inst_id = sa.inst_id
    AND s.sql_address = sa.address
    AND s.inst_id = i.inst_id
  ORDER BY
      wait_time_sec DESC
    , i.instance_name;
  