How to Change AWR Snapshot Retention Period & Interval Settings ( dba_hist_snapshot ) in Oracle


I will explain How to Change AWR Snapshot Retention & Interval Settings in Oracle in this Article.



Read previous article before this to learn What is the Oracle AWR report.

What is the Oracle AWR ( Automatic Workload Repository ) Report





AWR Snapshot’s default interval is 1 hour and retention is 8 days. It means All AWR statistics are collected every 1 hour and they are kept 8 days by default.


You can list AWR Snapshots by executing following command to see Snapshots and their Intervals.

SQL> select snap_id, begin_interval_time,end_interval_time from dba_hist_snapshot;

Execute following script to find out Snapshot interval Time and Retention Day.

SQL> select snap_interval,retention from dba_hist_wr_control;
SNAP_INTERVAL                                     RETENTION
 -------------------------------                 ------------------------- 
 +00000 01:00:00.0                                +00008 00:00:00.0




You can modify snapshot settings by using dbms_workload_repository package.

For example; Lets go to modify interval as 30 minutes and retention is 63 days like following.


SQL> exec dbms_workload_repository.modify_snapshot_settings ( interval => 30, retention => 90,720‬); --retention= 15*24*60=21600
PL/SQL procedure successfully completed.

I recommend that databases’ interval 30 minutes and retention more than 60 days to compare database with the past status.

Because When there is Performance problem in Oracle database, you need comparing with past days or you need comparing performance of TOP SQLs’ with past day or month.

So If you have enough disk space, keep AWR snapshots more than two or three months.




Normally, Snapshots are created automatically, but You can create Snapshot manually when you need it with the following script.


SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.

You can drop snapshots manually like following.

SQL> exec dbms_workload_repository.drop_snapshot_range (low_snap_id => 919, high_snap_id => 1125);
PL/SQL procedure successfully completed.



Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles. 

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *