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


DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google