How to Check AWR Snapshot Setting using SQL Queries?

advertisements

_____________________________________________________________________________________________________________________

a.   Query to check AWR snapshot interval and retention period set for the database. In below example the snapshot interval is one hour and retention is 8 days. The data type for the columns SNAP_INTERVAL RETENTION is having two fields – days and time with hour, min, sec.

select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL

---------- -------------------- -------------------- ---------

1313345459 +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT

2.  dba_hist_snapshot dictionary table holds the history of the snaps.

SELECT MIN(snap_id), min(BEGIN_INTERVAL_TIME),MAX(snap_id), max(BEGIN_INTERVAL_TIME) FROM dba_hist_snapshot

SQL> /

MIN(SNAP_ID) MIN(BEGIN_INTERVAL_TIME)  MAX(SNAP_ID) MAX(BEGIN_INTERVAL_TIME)

------------ ------------------------- ------------ -------------------------

       12214 2013-10-04 06:00:32,656          12405 2013-10-12 05:00:32,694

SQL> select snap_id, BEGIN_INTERVAL_TIME from dba_hist_snapshot;

   SNAP_ID BEGIN_INTERVAL_TIME

---------- ----------------------------------

     12284 2013-10-07 04:00:24,112

     12384 2013-10-11 08:00:44,261

     12285 2013-10-07 05:00:27,403

     12349 2013-10-09 21:00:15,047

     12386 2013-10-11 10:00:51,501

     12220 2013-10-04 12:00:57,177

     12221 2013-10-04 13:00:02,825

_____________________________________________________________________________________________________________________

Website Stats