Advertisement

Monday, April 24, 2017

Oracle Database - Historical Events / Wait Times / Statistics - AWR Data Mining & Extraction

 

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;


No comments:
Write comments