Tuesday, September 27, 2011

Modifying AWR Statistics Interval and Retention Periods


By using the DBMS_WORKLOAD_REPOSITORY PL/SQL package, you can modify the default snapshot settings for your database.

However, before that first validate your current retention and interval settings

SQL> column awr_snapshot_retention_period format a30

SQL> SELECT EXTRACT(day from retention) || ':' ||
  2         EXTRACT(hour from retention) || ':' ||
  3         EXTRACT (minute from retention)  awr_snapshot_retention_period,
  4         EXTRACT (day from snap_interval) *24*60+
  5         EXTRACT (hour from snap_interval) *60+
  6         EXTRACT (minute from snap_interval) awr_snapshot_interval
  7  FROM dba_hist_wr_control;

AWR_SNAPSHOT_RETENTION_PERIOD       AWR_SNAPSHOT_INTERVAL
----------------------------------- ---------------------
8:0:0                                                                                     60

Elapsed: 00:00:00.00
SQL>

based on above result. the retention period is set to 8 days, zero hours and zero minutes and interval is  set to 60 minutes meaning the updated stats will be gathered after every one hour.

So how to modify this setting ?

To modify the retention period and interval settings, use  MODIFY_SNAPSHOT_SETTINGS procedure of the DBMS_WORKLOAD_REPOSITORY package. 

For example, To modify the retention period to 30 days instead of 8 (specified by number of minutes) and interval to 30 minutes instead of 60, do the following

SQL> begin
  2  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>43200, interval=>30);
  3  end;
  4  /

PL/SQL procedure successfully completed.
to verify the changes, run the dba_hist_wr_control sql script again as show below

SQL> SELECT EXTRACT(day from retention) || ':' ||
  2         EXTRACT(hour from retention) || ':' ||
  3         EXTRACT (minute from retention)  awr_snapshot_retention_period,
  4         EXTRACT (day from snap_interval) *24*60+
  5         EXTRACT (hour from snap_interval) *60+
  6         EXTRACT (minute from snap_interval) awr_snapshot_interval
  7  FROM dba_hist_wr_control;

AWR_SNAPSHOT_RETENTION_PERIOD            AWR_SNAPSHOT_INTERVAL
---------------------------------------- ---------------------
30:0:0                                                                             30

...........................
To generate AWR report manually, read below article
http://bash-dba.blogspot.com/2011/08/awr-report-scenarios.html

To read AWR report properly read below article
http://bash-dba.blogspot.com/2011/09/how-to-read-awr-reports-1.html

No comments:

Post a Comment