---------------------------------------------------------------------
-- File Name : awr-datafile-latency-histogram.sql
-- Replace
-- Description : Datafile latency histogram from AWR tables
-- Last Modified: 08-June-2012
---------------------------------------------------------------------
SET LINES 80;
SET PAGES 80;
WITH HIST as (
SELECT
sn.snap_id,
sn.dbid,
to_char(trunc(cast(begin_interval_time as date ))+
(ROUND ((cast(begin_interval_time as date) -
TRUNC (cast(begin_interval_time as date))) * 24) / 24),
'YYYY/MM/DD HH24:MI') btime,
h.event_name,
h.wait_time_milli,
h.wait_count
FROM dba_hist_event_histogram h,
dba_hist_snapshot sn
WHERE
h.instance_number = 2
AND sn.instance_number = 2
AND h.event_name like 'db file seq%'
AND sn.snap_id=h.snap_id
AND sn.dbid=h.dbid
)
SELECT btime,
sum(MILLI_1) MILLI_1, sum(MILLI_2) MILLI_2,
sum(MILLI_4) MILLI_4, sum(MILLI_8) MILLI_8,
sum(MILLI_16) MILLI_16, sum(MILLI_32) MILLI_32,
sum(MILLI_64) MILLI_64, sum(MILLI_128) MILLI_128,
sum(MILLI_256) MILLI_256, sum(MILLI_512) MILLI_512
FROM
(SELECT btime,
case wait_time_milli WHEN 1 THEN WAIT_COUNT ELSE 0 END MILLI_1,
case wait_time_milli WHEN 2 THEN WAIT_COUNT ELSE 0 END MILLI_2,
case wait_time_milli WHEN 4 THEN WAIT_COUNT ELSE 0 END MILLI_4,
case wait_time_milli WHEN 8 THEN WAIT_COUNT ELSE 0 END MILLI_8,
case wait_time_milli WHEN 16 THEN WAIT_COUNT ELSE 0 END MILLI_16,
case wait_time_milli WHEN 32 THEN WAIT_COUNT ELSE 0 END MILLI_32,
case wait_time_milli WHEN 64 THEN WAIT_COUNT ELSE 0 END MILLI_64,
case wait_time_milli WHEN 128 THEN WAIT_COUNT ELSE 0 END MILLI_128,
case wait_time_milli WHEN 256 THEN WAIT_COUNT ELSE 0 END MILLI_256,
case wait_time_milli WHEN 512 THEN WAIT_COUNT ELSE 0 END MILLI_512
FROM
(SELECT a.btime,
a.wait_time_milli,
sum(b.wait_count - a.wait_count) wait_count
FROM hist a, hist b
WHERE a.dbid=b.dbid
AND a.snap_id=b.snap_id-1
AND a.wait_time_milli = b.wait_time_milli
GROUP BY a.btime, a.wait_time_milli
HAVING sum(b.wait_count - a.wait_count) > 0))
GROUP BY btime
ORDER BY 1, 2;
SQL> desc dba_hist_event_histogram
Name Null? Type
----------------------------------------- -------- ----------------------------
SNAP_ID NOT NULL NUMBER
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
EVENT_ID NOT NULL NUMBER
EVENT_NAME NOT NULL VARCHAR2(64)
WAIT_CLASS_ID NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_TIME_MILLI NOT NULL NUMBER
WAIT_COUNT NUMBER
Leave a Reply