How to change AWR retention, interval, topnsql?

Using dbms_workload_repository.modify_snapshot_settings one can modify retention, interval and topnsql.

— get the dbid which is needed to passs to dbms_workload_repository.modify_snapshot_settings
SQL> select dbid from v$database;


— retention=>value in minutes so (45 days * 24 (hours per day) * 60 minutes per hour = 64800), max value can be set by passing a value of 0 which means forever
— internal=>60min (snap at this interval), a value of 0 will turn off AWR
— topnsql – top N sql size, specify value of NULL will keep the current setting
SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>64800, interval=>60, topnsql=>100, dbid=>1992878807);

PL/SQL procedure successfully completed.

— shows retention and interval after it was modified
SQL> select extract( day from snap_interval) *24*60+extract( hour from snap_interval) *60+extract( minute from snap_interval ) snapshot_interval,
extract( day from retention) *24*60+extract( hour from retention) *60+extract( minute from retention ) retention_interval,
from dba_hist_wr_control;

Snapshot Interval Retention Interval topnsql
—————– —————— ———-
60 64800 100

-- Change snapshot interval to 30mins
SQL> EXEC dbms_workload_repository.modify_snapshot_settings(interval=>30);
PL/SQL procedure successfully completed.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.