83 lines
3.6 KiB
SQL
83 lines
3.6 KiB
SQL
set echo on
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display top wait events from STATSPACK
|
|
*
|
|
* Syntax : @file_name
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 15-May-07 Vishal Gupta First Draft
|
|
*
|
|
*/
|
|
set echo off
|
|
set verify off
|
|
set pages 10000
|
|
set lines 250
|
|
TTITLE CENTER "T O P E V E N T S T R E N D" -
|
|
SKIP 1 CENTER "################################" -
|
|
SKIP 1 CENTER ""
|
|
|
|
COLUMN snap_time HEADING "Snap Time" FORMAT a17
|
|
COLUMN E1 HEADING "1st|Event" FORMAT a30
|
|
COLUMN E2 HEADING "2nd|Event" FORMAT a30
|
|
COLUMN E3 HEADING "3rd|Event" FORMAT a30
|
|
COLUMN E4 HEADING "4th|Event" FORMAT a30
|
|
COLUMN E5 HEADING "5th|Event" FORMAT a30
|
|
COLUMN T1 HEADING "1st|Time" FORMAT 9999 noprint
|
|
COLUMN T2 HEADING "2nd|Time" FORMAT 9999 noprint
|
|
COLUMN T3 HEADING "3rd|Time" FORMAT 9999 noprint
|
|
COLUMN T4 HEADING "4th|Time" FORMAT 9999 noprint
|
|
COLUMN T5 HEADING "5th|Time" FORMAT 9999 noprint
|
|
COLUMN P1 HEADING "1st|%age" FORMAT a3
|
|
COLUMN P2 HEADING "2nd|%age" FORMAT a3
|
|
COLUMN P3 HEADING "3rd|%age" FORMAT a3
|
|
COLUMN P4 HEADING "4th|%age" FORMAT a3
|
|
COLUMN P5 HEADING "5th|%age" FORMAT a3
|
|
|
|
|
|
WITH topevents AS
|
|
(SELECT /*+ first_rows */
|
|
s.snap_time,
|
|
se.event,
|
|
ROUND((se.time_waited_micro -se2.time_waited_micro) / 1000000) AS
|
|
time_waited_delta,
|
|
dense_rank() over(PARTITION BY s.snap_time
|
|
ORDER BY se.time_waited_micro -se2.time_waited_micro DESC) rank
|
|
FROM stats$system_event se2,
|
|
stats$system_event se,
|
|
stats$snapshot s
|
|
WHERE s.snap_id = se.snap_id
|
|
AND s.dbid = se.dbid
|
|
AND s.instance_number = se.instance_number
|
|
-- AND to_char(s.snap_time, 'hh24MI') BETWEEN 0800 AND 0900
|
|
AND se2.snap_id = s.snap_id -1
|
|
AND s.dbid = se2.dbid
|
|
AND s.instance_number = se2.instance_number
|
|
AND se.event = se2.event
|
|
AND se.event NOT IN (select name from v$event_name where wait_class = 'Idle')
|
|
)
|
|
SELECT TO_CHAR(snap_time,'dd-mm-yy hh24:mi:ss') snap_time,
|
|
MAX(decode(rank, 1, event)) E1,
|
|
SUM(decode(rank, 1, time_waited_delta, 0)) T1,
|
|
ROUND(SUM(decode(rank, 1, time_waited_delta, 0)) /sum(time_waited_delta) *100 ,2)||'%' P1,
|
|
MAX(decode(rank, 2, event)) E2,
|
|
SUM(decode(rank, 2, time_waited_delta, 0)) T2,
|
|
ROUND(SUM(decode(rank, 2, time_waited_delta, 0)) /sum(time_waited_delta) *100 ,2)||'%' P2,
|
|
MAX(decode(rank, 3, event)) E3,
|
|
SUM(decode(rank, 3, time_waited_delta, 0)) T3,
|
|
ROUND(SUM(decode(rank, 3, time_waited_delta, 0)) /sum(time_waited_delta) *100 ,2)||'%' P3,
|
|
MAX(decode(rank, 4, event)) E4,
|
|
SUM(decode(rank, 4, time_waited_delta, 0)) T4,
|
|
ROUND(SUM(decode(rank, 4, time_waited_delta, 0)) /sum(time_waited_delta) *100 ,2)||'%' P4,
|
|
MAX(decode(rank, 5, event)) E5,
|
|
SUM(decode(rank, 5, time_waited_delta, 0)) T5,
|
|
ROUND(SUM(decode(rank, 5, time_waited_delta, 0)) /sum(time_waited_delta) *100 ,2)||'%' P5
|
|
FROM topevents t1
|
|
GROUP BY snap_time
|
|
ORDER BY snap_time DESC;
|
|
|