Friday, July 4, 2025

How to change Oracle AWR snapshots generation frequency

 The default frequency (automatic) of generation of Oracle Automatic Workload Repository (AWR) is every 1 hour (60 minutes). Sometimes, an Oracle DBA might have set this to generate more frequently, say, every 15 minutes i.e 4 times an hour to troubleshoot a performance issue and later would have forgotten to set it back to 1 hour frequency. 

This will fill up the SYSAUX tablespace very quickly. Please follow the below list of instructions to reset it back to 1 hour frequency.

This is tested in an Oracle 19c database.


select snap_interval, retention from dba_hist_wr_control;

If it is run every 15 minutes, then the output will show +00000 00:15:00.0

execute dbms_workload_repository.modify_snapshot_settings(interval => 60);

The AWR snapshots will now be generated every 1 hour instead of every 15 minutes.

select snap_interval, retention from dba_hist_wr_control;

The output should now show +00000 01:00:00.0


No comments: