@@header
set term off
/*
*
*  Author  : Vishal Gupta
*  Purpose : Display system event times from AWR
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  15-MAY-14  Vishal Gupta  Created
*/
set term on

/************************************
*  INPUT PARAMETERS
************************************/

UNDEFINE event_name
UNDEFINE days

DEFINE event_name="&&1"
DEFINE days="&&2"

COLUMN  _days                           NEW_VALUE  days                           NOPRINT
COLUMN  _force_matching_signature       NEW_VALUE  force_matching_signature       NOPRINT

set term off
SELECT DECODE('&&days','','3','&&days')                         "_days"
FROM DUAL;
set term on


Prompt 
Prompt **************************************************************
Prompt * AWR System Wide Wait Events (In seconds)
PROMPT * (Only snapshots with non-zero values are displayed)
PROMPT *  
PROMPT * Input Parameters 
PROMPT *    - Event Name       = '&&event_name'
PROMPT *    - Days             = '&&days'
Prompt **************************************************************

COLUMN end_interval_time             HEADING "Snap Time"              FORMAT a15
COLUMN all_Inst                      HEADING "All|Inst|(sec)"         FORMAT 999,999,999,999
COLUMN inst1                         HEADING "inst1|(sec)"            FORMAT 999,999,999
COLUMN inst2                         HEADING "inst2|(sec)"            FORMAT 999,999,999
COLUMN inst3                         HEADING "inst3|(sec)"            FORMAT 999,999,999
COLUMN inst4                         HEADING "inst4|(sec)"            FORMAT 999,999,999
COLUMN inst5                         HEADING "inst5|(sec)"            FORMAT 999,999,999
COLUMN inst6                         HEADING "inst6|(sec)"            FORMAT 999,999,999
COLUMN inst7                         HEADING "inst7|(sec)"            FORMAT 999,999,999
COLUMN inst8                         HEADING "inst8|(sec)"            FORMAT 999,999,999
COLUMN event_name                    HEADING "Event Name"             FORMAT a40 TRUNCATE

WITH snapshots AS
  (SELECT /*+ NO_MERGE  */
          s.dbid
        , s.instance_number
        , s.snap_id
        , s.end_interval_time
        ,  lag(s.snap_id) OVER (PARTITION BY s.dbid, s.instance_number ORDER BY s.snap_id) prev_snap_id
 FROM v$database d
      JOIN dba_hist_snapshot s ON s.dbid = d.dbid
     WHERE 1=1
      AND s.END_INTERVAL_TIME > sysdate - &days - (1/24)
   )
SELECT /*+ USE_NL(s e e_prev) */
       TO_CHAR(TRUNC(s.end_interval_time,'MI'),'DD-MON-YY HH24:MI') end_interval_time 
     , ROUND(SUM(DECODE(e.instance_number,1,(e.time_waited_micro - NVL( prev_e.time_waited_micro,0) )/1000000 ,0) )) All_Inst
     , ROUND(SUM(DECODE(e.instance_number,1,(e.time_waited_micro - NVL( prev_e.time_waited_micro,0) )/1000000 ,0) )) inst1
     , ROUND(SUM(DECODE(e.instance_number,2,(e.time_waited_micro - NVL( prev_e.time_waited_micro,0) )/1000000 ,0) )) inst2
     , ROUND(SUM(DECODE(e.instance_number,3,(e.time_waited_micro - NVL( prev_e.time_waited_micro,0) )/1000000 ,0) )) inst3
     , ROUND(SUM(DECODE(e.instance_number,4,(e.time_waited_micro - NVL( prev_e.time_waited_micro,0) )/1000000 ,0) )) inst4
     , ROUND(SUM(DECODE(e.instance_number,5,(e.time_waited_micro - NVL( prev_e.time_waited_micro,0) )/1000000 ,0) )) inst5
     , ROUND(SUM(DECODE(e.instance_number,6,(e.time_waited_micro - NVL( prev_e.time_waited_micro,0) )/1000000 ,0) )) inst6
     , ROUND(SUM(DECODE(e.instance_number,7,(e.time_waited_micro - NVL( prev_e.time_waited_micro,0) )/1000000 ,0) )) inst7
     , ROUND(SUM(DECODE(e.instance_number,8,(e.time_waited_micro - NVL( prev_e.time_waited_micro,0) )/1000000 ,0) )) inst8
     , e.event_name
 FROM snapshots s
      JOIN DBA_HIST_SYSTEM_EVENT e      ON e.dbid = s.dbid and e.instance_number = s.instance_number and e.snap_id = s.snap_id
      JOIN DBA_HIST_SYSTEM_EVENT prev_e ON prev_e.dbid = s.dbid and prev_e.instance_number = s.instance_number and prev_e.snap_id = s.prev_snap_id and e.event_id = prev_e.event_id
WHERE 1=1
and e.event_name like '&&event_name'
GROUP BY e.dbid
       , TRUNC(s.end_interval_time,'MI')
       , e.event_name
having 
    ROUND(SUM(DECODE(e.instance_number,1,(e.time_waited_micro - NVL( prev_e.time_waited_micro,0) )/1000000 ,0) )) > 0  
 OR ROUND(SUM(DECODE(e.instance_number,2,(e.time_waited_micro - NVL( prev_e.time_waited_micro,0) )/1000000 ,0) )) > 0  
 OR ROUND(SUM(DECODE(e.instance_number,3,(e.time_waited_micro - NVL( prev_e.time_waited_micro,0) )/1000000 ,0) )) > 0 
 OR ROUND(SUM(DECODE(e.instance_number,4,(e.time_waited_micro - NVL( prev_e.time_waited_micro,0) )/1000000 ,0) )) > 0 
 OR ROUND(SUM(DECODE(e.instance_number,5,(e.time_waited_micro - NVL( prev_e.time_waited_micro,0) )/1000000 ,0) )) > 0 
 OR ROUND(SUM(DECODE(e.instance_number,6,(e.time_waited_micro - NVL( prev_e.time_waited_micro,0) )/1000000 ,0) )) > 0 
 OR ROUND(SUM(DECODE(e.instance_number,7,(e.time_waited_micro - NVL( prev_e.time_waited_micro,0) )/1000000 ,0) )) > 0 
 OR ROUND(SUM(DECODE(e.instance_number,8,(e.time_waited_micro - NVL( prev_e.time_waited_micro,0) )/1000000 ,0) )) > 0 
ORDER BY e.dbid
       , TRUNC(s.end_interval_time,'MI')
       , e.event_name
;

@@footer