--  FILE:   waits.sql
--
--  AUTHOR: Andy Rivenes, arivenes@appsdba.com
--          Copyright (c) 1998-2001, AppsDBA Consulting.  All Rights Reserved.
--
--  DATE:   05/02/1996
--
--  DESCRIPTION:
--          Query to show how the ORACLE kernel is using its time.
--          
--  MODIFICATIONS:
--          A. Rivenes, 12/10/1997, Formatted output.
--          A. Rivenes, 03/22/1999, Added more details.
--          A. Rivenes, 05/03/2001, Refined the queries to help drive to
--                                  session level waiters.
--
-- 
-- This program is free software: you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation, either version 3 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program.  If not, see <http://www.gnu.org/licenses/>.
-- 
--
SET PAGESIZE 9999;
SET LINESIZE 132;
SPOOL waits.txt;
--
PROMPT ;
PROMPT > List Session Event Summary ;
COLUMN event           HEADING 'Event'                 FORMAT A35;
COLUMN totw            HEADING 'Total|Waits'           FORMAT 999999999999;
COLUMN tottm           HEADING 'Total|Timeouts'        FORMAT 99999999;
COLUMN tw              HEADING 'Time|Waited(sec)'      FORMAT 9999999999990.99;
COLUMN aw              HEADING 'Avg|Wait(cs)'          FORMAT 999999;
COLUMN mw              HEADING 'Max|Wait(cs)'          FORMAT 999999999;
 SELECT event,
        SUM(total_waits) totw,
        SUM(total_timeouts) tottm,
        SUM(time_waited)/100 tw,
        AVG(average_wait) aw,
        MAX(max_wait) mw 
   FROM v$session_event
  GROUP BY event
  ORDER BY SUM(time_waited) DESC;
--
--
PROMPT ;
PROMPT > List Current Session Wait Events ;
COLUMN event           HEADING 'Wait Event'           FORMAT A40  WORD_WRAPPED;
COLUMN tot_sess        HEADING 'Sessions|Waiting'     FORMAT 99999999999;
COLUMN tot_sec         HEADING 'Seconds|Waited'       FORMAT 99999999999;
SELECT event,
       COUNT(event) tot_sess,
       SUM(seconds_in_wait) tot_sec
  FROM v$session_wait
 WHERE state = 'WAITING'
 GROUP BY event
 ORDER BY tot_sec DESC;
--
--
PROMPT ;
PROMPT > List Current Session Wait Events Summary ;
COLUMN sid             HEADING 'SID'                   FORMAT 99999;
COLUMN event           HEADING 'Event'                 FORMAT A35;
COLUMN total_waits     HEADING 'Total|Waits'           FORMAT 99999999;
COLUMN total_timeouts  HEADING 'Total|Timeouts'        FORMAT 99999999;
COLUMN tw              HEADING 'Time|Waited(sec)'      FORMAT 999999990.99;
COLUMN average_wait    HEADING 'Avg|Wait(cs)'          FORMAT 999999;
BREAK ON sid;
 SELECT se.sid,
        se.event,
        se.total_waits,
        se.total_timeouts,
        se.time_waited/100 tw,
        se.average_wait
   FROM v$session_event se,
        v$session_wait sw
  WHERE se.sid = sw.sid
    AND sw.state = 'WAITING'
    AND sw.event NOT LIKE 'SQL*Net%'
    AND sw.event != 'rdbms ipc message'
  ORDER BY sid,
        time_waited DESC;
--
--
PROMPT ;
PROMPT > List Current Session Wait Details ;
PROMPT >  Skips SQL*Net and rdbms ipc message waits ;
COLUMN sid             HEADING 'SID'                  FORMAT 9999;
COLUMN event           HEADING 'Wait Event'           FORMAT A15  WORD_WRAPPED;
COLUMN state           HEADING 'Wait State'           FORMAT A10  WORD_WRAPPED;
COLUMN seconds_in_wait HEADING 'Seconds|Waited'       FORMAT 9999999999999;
COLUMN p1              HEADING 'P1'                   FORMAT 9999999999999;
COLUMN p1text          HEADING 'P1 Text'              FORMAT A10  WORD_WRAPPED;
COLUMN p2              HEADING 'P2'                   FORMAT 9900009999999;
COLUMN p2text          HEADING 'P2 Text'              FORMAT A10  WORD_WRAPPED;
COLUMN p3              HEADING 'P3'                   FORMAT 99999;
COLUMN p3text          HEADING 'P3 Text'              FORMAT A10  WORD_WRAPPED;
BREAK ON sid;
SELECT sid, 
       event,
       state,
       seconds_in_wait,
       p1,
       p1text,
       p2,
       p2text,
       p3,
       p3text
  FROM v$session_wait
 WHERE state = 'WAITING'
   AND event NOT LIKE 'SQL*Net%'
   AND event != 'rdbms ipc message'
 ORDER BY sid, 
       wait_time DESC;
--
--
SPOOL off;