How to Check AWR Snapshot Setting using SQL Queries?

_____________________________________________________________________________________________________________________

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


  1.  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


_____________________________________________________________________________________________________________________

0 comments:

Post a Comment

 

acehints.com Copyright 2011-17 All Rights Reserved | Site Map | Contact | Disclaimer | Google