In this blog I am going to discuss on how to mine and extract data from AWR Tables.
In my previous blog I have discussed how to extract event / statistics currently (from V$views)
Oracle Database - Current Wait Events / Metrics Statistics
I will be discussing 3 particular AWR Views / Tables for extraction of data.
DBA_HIST_SYSMETRIC_SUMMARY
This view contains historical information for sysmetric V$ view. It is useful in getting important data such as Buffer Cache Hit Ratio. This view is not cumulative, it is helpful in finding min/max/average values over period of time as a function of snap_id / time.
DBA_HIST_SYSTEM_EVENT
This view is historical view to V$System_Event, the event statistics in V$system_event are snapped and kept in this view as per the AWR snapshot period. This event since is a snap of V$system_event is obviously cumulative, so to make sense or get the deltas, values should be subtracted from previous value
DBA_HIST_EVENT_HISTOGRAM
This view is a snap of V$event_histogram, this view is again cumulative, so to find values for a one snap period period, values should be subtracted from previous snaps value.
DBA_HIST_FILESTATXS
This view is historical view to V$filestat, the event statistics in V$filestat are snapped and kept in the view. this view is also cumulative.
Below are queries to get data from these views.
I have written the queries in a such a way to get the # of days you want to look data for, the # of days can be like 1/24 signifying 1 hour.
If the values of any of the historical stats are negative (in case of cumulative stats), that means instance was restarted before that snap.
Find out Buffer Cache Hit Ratio over Period of time
set lines 500 pages 500
col BEGIN_INTERVAL_TIME for a30
col METRIC_NAME for a30
col METRIC_UNIT for a30
WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
DHS.SNAP_ID, DHS.BEGIN_INTERVAL_TIME,
DHSYSM.METRIC_NAME, round(DHSYSM.MAXVAL,2) MAX_VAL , round(DHSYSM.MINVAL,2) MIN_VAL, round(DHSYSM.AVERAGE,2) AVG_VAL , METRIC_UNIT
FROM DBA_HIST_SNAPSHOT DHS, DBA_HIST_SYSMETRIC_SUMMARY DHSYSM , MN_SNAP_ID MNS
WHERE
DHS.SNAP_ID = DHSYSM.SNAP_ID AND
DHS.SNAP_ID > MNS.SNAP_ID AND
METRIC_NAME = '&Metric_Name'
ORDER BY SNAP_ID;
Find out Log file Sync Time over Period of time
set lines 500 pages 500
col BEGIN_TIME for a30
col EVENT_NAME for a40
WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
SNAP_ID, BEGIN_TIME, EVENT_NAME , TOTAL_WAITS, round(TOTAL_TIME_WAITED_MICRO/1000000,2) TOTAL_TIME_WAITED_SEC, round(TOTAL_TIME_WAITED_MICRO/TOTAL_WAITS/1000,2) AVG_TIME_WAITED_MS
FROM
(
SELECT
DHS.snap_id, DHS.BEGIN_INTERVAL_TIME BEGIN_TIME, DHSE.EVENT_NAME,
TOTAL_WAITS - LAG(TOTAL_WAITS, 1, 0) OVER (ORDER BY DHS.SNAP_ID) TOTAL_WAITS,
TIME_WAITED_MICRO - LAG(TIME_WAITED_MICRO, 1, 0) OVER (ORDER BY DHS.SNAP_ID) TOTAL_TIME_WAITED_MICRO
FROM
DBA_HIST_SYSTEM_EVENT DHSE, DBA_HIST_SNAPSHOT DHS, MN_SNAP_ID MNS
WHERE
EVENT_NAME = '&event_name' AND
DHS.SNAP_ID = DHSE.SNAP_ID AND
DHS.SNAP_ID > MNS.SNAP_ID
)
ORDER BY SNAP_ID;
Find out log file sync histogram details
SQL> break on snap_id on BEGIN_TIME skip 1
SQL> col BEGIN_TIME format a30
SQL> var snapid number;
SQL> EXEC SELECT MIN(SNAP_ID) INTO :SNAPID FROM DBA_HIST_SNAPSHOT WHERE BEGIN_INTERVAL_TIME > sysdate - &days;
Enter value for days: 1/12
PL/SQL procedure successfully completed.
SQL> r
with hist as (
select
sn.snap_id,
begin_interval_time 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 = &inst_num
and sn.instance_number = h.instance_number
and h.event_name = '&event_name'
and sn.snap_id=h.snap_id
and sn.snap_id > :snapid
)
select a.snap_id, a.btime BEGIN_TIME,
a.wait_time_milli,
b.wait_count - a.wait_count WAIT_COUNT
from hist a,
hist b
where
a.snap_id=b.snap_id-1
and a.wait_time_milli = b.wait_time_milli
order by a.snap_id, WAIT_TIME_MILLI
Enter value for inst_num: 1
old 11: h.instance_number = &inst_num
new 11: h.instance_number = 1
Enter value for event_name: log file sync
old 13: and h.event_name = '&event_name'
new 13: and h.event_name = 'log file sync'
SNAP_ID BEGIN_TIME WAIT_TIME_MILLI WAIT_COUNT
---------- ------------------------------ --------------- ----------
278182 25-APR-17 06.00.47.887 AM 1 26272
2 9054
4 6352
8 2734
16 827
32 273
64 27
128 1
256 0
512 0
1024 0
2048 0
4096 0
8192 0
16384 0
278183 25-APR-17 06.15.50.075 AM 1 28470
2 9173
4 5343
8 1506
16 375
32 215
64 34
128 0
256 0
512 0
1024 0
2048 0
4096 0
8192 0
16384 0
278184 25-APR-17 06.30.51.845 AM 1 28746
2 7457
4 3772
8 1121
16 369
32 214
64 26
128 2
256 0
512 0
1024 0
2048 0
4096 0
8192 0
16384 0
278185 25-APR-17 06.45.53.497 AM 1 11102
2 10205
4 13808
8 10206
16 3895
32 4013
64 4255
128 2664
256 796
512 1679
1024 2481
2048 3255
4096 994
8192 36
16384 0
278186 25-APR-17 07.00.55.247 AM 1 17512
2 11811
4 10061
8 4623
16 1204
32 455
64 89
128 19
256 0
512 0
1024 0
2048 0
4096 0
8192 0
16384 0
Find out File Statistics over Period of time
set lines 500 pages 500
col BEGIN_TIME for a30
break on FILE# SKIP 1
WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT FILE#, SNAP_ID, BEGIN_TIME,
round(WRITETM * 10 / PHYWRTS,2) AS AVG_WRT_MS,
round(READTIM * 10 /PHYRDS,2) AS AVG_RD_MS FROM
(
SELECT FILE#,
DHS.SNAP_ID, DHS.BEGIN_INTERVAL_TIME BEGIN_TIME,
PHYWRTS - LAG(PHYWRTS, 1, 0) OVER (PARTITION BY FILE# ORDER BY DHS.SNAP_ID) PHYWRTS,
WRITETIM - LAG(WRITETIM, 1, 0) OVER (PARTITION BY FILE# ORDER BY DHS.SNAP_ID) WRITETM,
PHYRDS - LAG(PHYRDS, 1, 0) OVER (PARTITION BY FILE# ORDER BY DHS.SNAP_ID) PHYRDS,
READTIM - LAG(READTIM, 1, 0) OVER (PARTITION BY FILE# ORDER BY DHS.SNAP_ID) READTIM
FROM DBA_HIST_FILESTATXS DHSE, DBA_HIST_SNAPSHOT DHS, MN_SNAP_ID MNS
WHERE FILE# IN (&file_id) AND
DHS.SNAP_ID = DHSE.SNAP_ID AND
DHS.SNAP_ID > MNS.SNAP_ID
)
ORDER BY FILE#, SNAP_ID;
Query Variations for RAC and specific Instance
DBA_HIST_SYSMETRIC Summary
Specific Instance
set lines 500 pages 500
col BEGIN_INTERVAL_TIME for a30
col METRIC_NAME for a30
col METRIC_UNIT for a30
WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
DHS.SNAP_ID, DHS.BEGIN_INTERVAL_TIME,
DHSYSM.METRIC_NAME, round(DHSYSM.MAXVAL,2) MAX_VAL , round(DHSYSM.MINVAL,2) MIN_VAL, round(DHSYSM.AVERAGE,2) AVG_VAL , METRIC_UNIT
FROM DBA_HIST_SNAPSHOT DHS, DBA_HIST_SYSMETRIC_SUMMARY DHSYSM , MN_SNAP_ID MNS
WHERE
DHS.SNAP_ID = DHSYSM.SNAP_ID AND
DHS.SNAP_ID > MNS.SNAP_ID AND
METRIC_NAME = '&Metric_Name' AND
DHSYSM.INSTANCE_NUMBER = DHS.INSTANCE_NUMBER AND
DHSYSM.INSTANCE_NUMBER = &instance_number
ORDER BY SNAP_ID;
RAC All Instances
set lines 500 pages 500
col BEGIN_INTERVAL_TIME for a30
col METRIC_NAME for a30
col METRIC_UNIT for a30
WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
DHSYSM.INSTANCE_NUMBER, DHS.SNAP_ID, DHS.BEGIN_INTERVAL_TIME,
DHSYSM.METRIC_NAME, round(DHSYSM.MAXVAL,2) MAX_VAL , round(DHSYSM.MINVAL,2) MIN_VAL, round(DHSYSM.AVERAGE,2) AVG_VAL , METRIC_UNIT
FROM DBA_HIST_SNAPSHOT DHS, DBA_HIST_SYSMETRIC_SUMMARY DHSYSM , MN_SNAP_ID MNS
WHERE
DHS.SNAP_ID = DHSYSM.SNAP_ID AND
DHS.SNAP_ID > MNS.SNAP_ID AND
METRIC_NAME = '&Metric_Name' AND
DHSYSM.INSTANCE_NUMBER = DHS.INSTANCE_NUMBER
ORDER BY DHSYSM.INSTANCE_NUMBER, SNAP_ID;
DBA_HIST_SYSTEM_EVENT
Specific Instance
set lines 500 pages 500
col BEGIN_TIME for a30
col EVENT_NAME for a40
WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
SNAP_ID, BEGIN_TIME, EVENT_NAME , TOTAL_WAITS, round(TOTAL_TIME_WAITED_MICRO/1000000,2) TOTAL_TIME_WAITED_SEC, round(TOTAL_TIME_WAITED_MICRO/TOTAL_WAITS/1000,2) AVG_TIME_WAITED_MS
FROM
(
SELECT
DHS.snap_id, DHS.BEGIN_INTERVAL_TIME BEGIN_TIME, DHSE.EVENT_NAME,
TOTAL_WAITS - LAG(TOTAL_WAITS, 1, 0) OVER (ORDER BY DHS.SNAP_ID) TOTAL_WAITS,
TIME_WAITED_MICRO - LAG(TIME_WAITED_MICRO, 1, 0) OVER (ORDER BY DHS.SNAP_ID) TOTAL_TIME_WAITED_MICRO
FROM
DBA_HIST_SYSTEM_EVENT DHSE, DBA_HIST_SNAPSHOT DHS, MN_SNAP_ID MNS
WHERE
EVENT_NAME = '&event_name' AND
DHS.SNAP_ID = DHSE.SNAP_ID AND
DHS.SNAP_ID > MNS.SNAP_ID AND
DHSE.INSTANCE_NUMBER = DHS.INSTANCE_NUMBER AND
DHSE.INSTANCE_NUMBER = &instance_number
)
ORDER BY SNAP_ID;
RAC All Instances
set lines 500 pages 500
col BEGIN_TIME for a30
col EVENT_NAME for a40
WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
INSTANCE_NUMBER, SNAP_ID, BEGIN_TIME, EVENT_NAME ,
TOTAL_WAITS, round(TOTAL_TIME_WAITED_MICRO/1000000,2) TOTAL_TIME_WAITED_SEC,
round(TOTAL_TIME_WAITED_MICRO/TOTAL_WAITS/1000,2) AVG_TIME_WAITED_MS
FROM
(
SELECT
DHSE.INSTANCE_NUMBER,DHS.snap_id, DHS.BEGIN_INTERVAL_TIME BEGIN_TIME, DHSE.EVENT_NAME,
TOTAL_WAITS - LAG(TOTAL_WAITS, 1, 0) OVER (PARTITION BY DHSE.INSTANCE_NUMBER ORDER BY DHS.SNAP_ID) TOTAL_WAITS,
TIME_WAITED_MICRO - LAG(TIME_WAITED_MICRO, 1, 0) OVER (PARTITION BY DHSE.INSTANCE_NUMBER ORDER BY DHS.SNAP_ID) TOTAL_TIME_WAITED_MICRO
FROM
DBA_HIST_SYSTEM_EVENT DHSE, DBA_HIST_SNAPSHOT DHS, MN_SNAP_ID MNS
WHERE
EVENT_NAME = '&event_name' AND
DHS.SNAP_ID = DHSE.SNAP_ID AND
DHS.SNAP_ID > MNS.SNAP_ID AND
DHSE.INSTANCE_NUMBER = DHS.INSTANCE_NUMBER
)
ORDER BY INSTANCE_NUMBER, SNAP_ID;
In my previous blog I have discussed how to extract event / statistics currently (from V$views)
Oracle Database - Current Wait Events / Metrics Statistics
I will be discussing 3 particular AWR Views / Tables for extraction of data.
DBA_HIST_SYSMETRIC_SUMMARY
This view contains historical information for sysmetric V$ view. It is useful in getting important data such as Buffer Cache Hit Ratio. This view is not cumulative, it is helpful in finding min/max/average values over period of time as a function of snap_id / time.
DBA_HIST_SYSTEM_EVENT
This view is historical view to V$System_Event, the event statistics in V$system_event are snapped and kept in this view as per the AWR snapshot period. This event since is a snap of V$system_event is obviously cumulative, so to make sense or get the deltas, values should be subtracted from previous value
DBA_HIST_EVENT_HISTOGRAM
This view is a snap of V$event_histogram, this view is again cumulative, so to find values for a one snap period period, values should be subtracted from previous snaps value.
DBA_HIST_FILESTATXS
This view is historical view to V$filestat, the event statistics in V$filestat are snapped and kept in the view. this view is also cumulative.
Below are queries to get data from these views.
I have written the queries in a such a way to get the # of days you want to look data for, the # of days can be like 1/24 signifying 1 hour.
If the values of any of the historical stats are negative (in case of cumulative stats), that means instance was restarted before that snap.
Find out Buffer Cache Hit Ratio over Period of time
set lines 500 pages 500
col BEGIN_INTERVAL_TIME for a30
col METRIC_NAME for a30
col METRIC_UNIT for a30
WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
DHS.SNAP_ID, DHS.BEGIN_INTERVAL_TIME,
DHSYSM.METRIC_NAME, round(DHSYSM.MAXVAL,2) MAX_VAL , round(DHSYSM.MINVAL,2) MIN_VAL, round(DHSYSM.AVERAGE,2) AVG_VAL , METRIC_UNIT
FROM DBA_HIST_SNAPSHOT DHS, DBA_HIST_SYSMETRIC_SUMMARY DHSYSM , MN_SNAP_ID MNS
WHERE
DHS.SNAP_ID = DHSYSM.SNAP_ID AND
DHS.SNAP_ID > MNS.SNAP_ID AND
METRIC_NAME = '&Metric_Name'
ORDER BY SNAP_ID;
Find out Log file Sync Time over Period of time
set lines 500 pages 500
col BEGIN_TIME for a30
col EVENT_NAME for a40
WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
SNAP_ID, BEGIN_TIME, EVENT_NAME , TOTAL_WAITS, round(TOTAL_TIME_WAITED_MICRO/1000000,2) TOTAL_TIME_WAITED_SEC, round(TOTAL_TIME_WAITED_MICRO/TOTAL_WAITS/1000,2) AVG_TIME_WAITED_MS
FROM
(
SELECT
DHS.snap_id, DHS.BEGIN_INTERVAL_TIME BEGIN_TIME, DHSE.EVENT_NAME,
TOTAL_WAITS - LAG(TOTAL_WAITS, 1, 0) OVER (ORDER BY DHS.SNAP_ID) TOTAL_WAITS,
TIME_WAITED_MICRO - LAG(TIME_WAITED_MICRO, 1, 0) OVER (ORDER BY DHS.SNAP_ID) TOTAL_TIME_WAITED_MICRO
FROM
DBA_HIST_SYSTEM_EVENT DHSE, DBA_HIST_SNAPSHOT DHS, MN_SNAP_ID MNS
WHERE
EVENT_NAME = '&event_name' AND
DHS.SNAP_ID = DHSE.SNAP_ID AND
DHS.SNAP_ID > MNS.SNAP_ID
)
ORDER BY SNAP_ID;
Find out log file sync histogram details
SQL> break on snap_id on BEGIN_TIME skip 1
SQL> col BEGIN_TIME format a30
SQL> var snapid number;
SQL> EXEC SELECT MIN(SNAP_ID) INTO :SNAPID FROM DBA_HIST_SNAPSHOT WHERE BEGIN_INTERVAL_TIME > sysdate - &days;
Enter value for days: 1/12
PL/SQL procedure successfully completed.
SQL> r
with hist as (
select
sn.snap_id,
begin_interval_time 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 = &inst_num
and sn.instance_number = h.instance_number
and h.event_name = '&event_name'
and sn.snap_id=h.snap_id
and sn.snap_id > :snapid
)
select a.snap_id, a.btime BEGIN_TIME,
a.wait_time_milli,
b.wait_count - a.wait_count WAIT_COUNT
from hist a,
hist b
where
a.snap_id=b.snap_id-1
and a.wait_time_milli = b.wait_time_milli
order by a.snap_id, WAIT_TIME_MILLI
Enter value for inst_num: 1
old 11: h.instance_number = &inst_num
new 11: h.instance_number = 1
Enter value for event_name: log file sync
old 13: and h.event_name = '&event_name'
new 13: and h.event_name = 'log file sync'
SNAP_ID BEGIN_TIME WAIT_TIME_MILLI WAIT_COUNT
---------- ------------------------------ --------------- ----------
278182 25-APR-17 06.00.47.887 AM 1 26272
2 9054
4 6352
8 2734
16 827
32 273
64 27
128 1
256 0
512 0
1024 0
2048 0
4096 0
8192 0
16384 0
278183 25-APR-17 06.15.50.075 AM 1 28470
2 9173
4 5343
8 1506
16 375
32 215
64 34
128 0
256 0
512 0
1024 0
2048 0
4096 0
8192 0
16384 0
278184 25-APR-17 06.30.51.845 AM 1 28746
2 7457
4 3772
8 1121
16 369
32 214
64 26
128 2
256 0
512 0
1024 0
2048 0
4096 0
8192 0
16384 0
278185 25-APR-17 06.45.53.497 AM 1 11102
2 10205
4 13808
8 10206
16 3895
32 4013
64 4255
128 2664
256 796
512 1679
1024 2481
2048 3255
4096 994
8192 36
16384 0
278186 25-APR-17 07.00.55.247 AM 1 17512
2 11811
4 10061
8 4623
16 1204
32 455
64 89
128 19
256 0
512 0
1024 0
2048 0
4096 0
8192 0
16384 0
Find out File Statistics over Period of time
set lines 500 pages 500
col BEGIN_TIME for a30
break on FILE# SKIP 1
WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT FILE#, SNAP_ID, BEGIN_TIME,
round(WRITETM * 10 / PHYWRTS,2) AS AVG_WRT_MS,
round(READTIM * 10 /PHYRDS,2) AS AVG_RD_MS FROM
(
SELECT FILE#,
DHS.SNAP_ID, DHS.BEGIN_INTERVAL_TIME BEGIN_TIME,
PHYWRTS - LAG(PHYWRTS, 1, 0) OVER (PARTITION BY FILE# ORDER BY DHS.SNAP_ID) PHYWRTS,
WRITETIM - LAG(WRITETIM, 1, 0) OVER (PARTITION BY FILE# ORDER BY DHS.SNAP_ID) WRITETM,
PHYRDS - LAG(PHYRDS, 1, 0) OVER (PARTITION BY FILE# ORDER BY DHS.SNAP_ID) PHYRDS,
READTIM - LAG(READTIM, 1, 0) OVER (PARTITION BY FILE# ORDER BY DHS.SNAP_ID) READTIM
FROM DBA_HIST_FILESTATXS DHSE, DBA_HIST_SNAPSHOT DHS, MN_SNAP_ID MNS
WHERE FILE# IN (&file_id) AND
DHS.SNAP_ID = DHSE.SNAP_ID AND
DHS.SNAP_ID > MNS.SNAP_ID
)
ORDER BY FILE#, SNAP_ID;
Query Variations for RAC and specific Instance
DBA_HIST_SYSMETRIC Summary
Specific Instance
set lines 500 pages 500
col BEGIN_INTERVAL_TIME for a30
col METRIC_NAME for a30
col METRIC_UNIT for a30
WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
DHS.SNAP_ID, DHS.BEGIN_INTERVAL_TIME,
DHSYSM.METRIC_NAME, round(DHSYSM.MAXVAL,2) MAX_VAL , round(DHSYSM.MINVAL,2) MIN_VAL, round(DHSYSM.AVERAGE,2) AVG_VAL , METRIC_UNIT
FROM DBA_HIST_SNAPSHOT DHS, DBA_HIST_SYSMETRIC_SUMMARY DHSYSM , MN_SNAP_ID MNS
WHERE
DHS.SNAP_ID = DHSYSM.SNAP_ID AND
DHS.SNAP_ID > MNS.SNAP_ID AND
METRIC_NAME = '&Metric_Name' AND
DHSYSM.INSTANCE_NUMBER = DHS.INSTANCE_NUMBER AND
DHSYSM.INSTANCE_NUMBER = &instance_number
ORDER BY SNAP_ID;
RAC All Instances
set lines 500 pages 500
col BEGIN_INTERVAL_TIME for a30
col METRIC_NAME for a30
col METRIC_UNIT for a30
WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
DHSYSM.INSTANCE_NUMBER, DHS.SNAP_ID, DHS.BEGIN_INTERVAL_TIME,
DHSYSM.METRIC_NAME, round(DHSYSM.MAXVAL,2) MAX_VAL , round(DHSYSM.MINVAL,2) MIN_VAL, round(DHSYSM.AVERAGE,2) AVG_VAL , METRIC_UNIT
FROM DBA_HIST_SNAPSHOT DHS, DBA_HIST_SYSMETRIC_SUMMARY DHSYSM , MN_SNAP_ID MNS
WHERE
DHS.SNAP_ID = DHSYSM.SNAP_ID AND
DHS.SNAP_ID > MNS.SNAP_ID AND
METRIC_NAME = '&Metric_Name' AND
DHSYSM.INSTANCE_NUMBER = DHS.INSTANCE_NUMBER
ORDER BY DHSYSM.INSTANCE_NUMBER, SNAP_ID;
DBA_HIST_SYSTEM_EVENT
Specific Instance
set lines 500 pages 500
col BEGIN_TIME for a30
col EVENT_NAME for a40
WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
SNAP_ID, BEGIN_TIME, EVENT_NAME , TOTAL_WAITS, round(TOTAL_TIME_WAITED_MICRO/1000000,2) TOTAL_TIME_WAITED_SEC, round(TOTAL_TIME_WAITED_MICRO/TOTAL_WAITS/1000,2) AVG_TIME_WAITED_MS
FROM
(
SELECT
DHS.snap_id, DHS.BEGIN_INTERVAL_TIME BEGIN_TIME, DHSE.EVENT_NAME,
TOTAL_WAITS - LAG(TOTAL_WAITS, 1, 0) OVER (ORDER BY DHS.SNAP_ID) TOTAL_WAITS,
TIME_WAITED_MICRO - LAG(TIME_WAITED_MICRO, 1, 0) OVER (ORDER BY DHS.SNAP_ID) TOTAL_TIME_WAITED_MICRO
FROM
DBA_HIST_SYSTEM_EVENT DHSE, DBA_HIST_SNAPSHOT DHS, MN_SNAP_ID MNS
WHERE
EVENT_NAME = '&event_name' AND
DHS.SNAP_ID = DHSE.SNAP_ID AND
DHS.SNAP_ID > MNS.SNAP_ID AND
DHSE.INSTANCE_NUMBER = DHS.INSTANCE_NUMBER AND
DHSE.INSTANCE_NUMBER = &instance_number
)
ORDER BY SNAP_ID;
RAC All Instances
set lines 500 pages 500
col BEGIN_TIME for a30
col EVENT_NAME for a40
WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
INSTANCE_NUMBER, SNAP_ID, BEGIN_TIME, EVENT_NAME ,
TOTAL_WAITS, round(TOTAL_TIME_WAITED_MICRO/1000000,2) TOTAL_TIME_WAITED_SEC,
round(TOTAL_TIME_WAITED_MICRO/TOTAL_WAITS/1000,2) AVG_TIME_WAITED_MS
FROM
(
SELECT
DHSE.INSTANCE_NUMBER,DHS.snap_id, DHS.BEGIN_INTERVAL_TIME BEGIN_TIME, DHSE.EVENT_NAME,
TOTAL_WAITS - LAG(TOTAL_WAITS, 1, 0) OVER (PARTITION BY DHSE.INSTANCE_NUMBER ORDER BY DHS.SNAP_ID) TOTAL_WAITS,
TIME_WAITED_MICRO - LAG(TIME_WAITED_MICRO, 1, 0) OVER (PARTITION BY DHSE.INSTANCE_NUMBER ORDER BY DHS.SNAP_ID) TOTAL_TIME_WAITED_MICRO
FROM
DBA_HIST_SYSTEM_EVENT DHSE, DBA_HIST_SNAPSHOT DHS, MN_SNAP_ID MNS
WHERE
EVENT_NAME = '&event_name' AND
DHS.SNAP_ID = DHSE.SNAP_ID AND
DHS.SNAP_ID > MNS.SNAP_ID AND
DHSE.INSTANCE_NUMBER = DHS.INSTANCE_NUMBER
)
ORDER BY INSTANCE_NUMBER, SNAP_ID;
No comments:
Write comments