104 lines
5.3 KiB
SQL
104 lines
5.3 KiB
SQL
@@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
|