Friday, 2 August 2013

How to check and modify the AWR settings?

I've decided to write this post, since every time I need to check/modify the AWR snapshot settings, I never remember the exact views and procedures to use.
So here they are:

1) Getting the current AWR snapshot settings:

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL                                                               RETENTION                                                                   TOPNSQL
---------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ----------
2560013153 +00000 00:10:00.0                                                           +00010 00:00:00.0                                                           DEFAULT


What this means is that the snapshots are running every 10 minutes and the retention time is 10 days.


2) Modifying the snapshot interval to every 15 minutes and retention time to 31 days only:

SQL> execute dbms_workload_repository.modify_snapshot_settings( interval => 15,retention => 44640);


 interval is in minutes, retention also.


SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL                                                               RETENTION                                                                   TOPNSQL
---------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ----------
2560013153 +00000 00:15:00.0                                                           +000031 00:00:00.0                                                           DEFAULT

No comments:

Post a Comment