检查AWR当前设置:

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL             RETENTION                 TOPNSQL

---------- ------------------------- ------------------------- ----------

 952006016 +00000 00:30:00.0         +00015 00:00:00.0         DEFAULT

修改设置:

SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>14400,interval=>60);

PL/SQL procedure successfully completed.

修改后检查设置:

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL             RETENTION                 TOPNSQL

---------- ------------------------- ------------------------- ----------

 952006016 +00000 01:00:00.0         +00010 00:00:00.0         DEFAULT