Default Snapshot Setting

Metadata about snapshots is recorded in the DBA_HIST_SNAPSHOT view. Below is an example of querying the view:

SELECT snap_id,
       dbid,
       instance_number,
       begin_interval_time,
       end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id;

A sample output:

...
  SNAP_ID         DBID   INSTANCE_NUMBER BEGIN_INTERVAL_TIME               END_INTERVAL_TIME
     1608   1698705135                 1 19-SEP-24 04.00.30.351000000 AM   19-SEP-24 05.00.33.189000000 AM
     1609   1698705135                 1 19-SEP-24 05.00.33.189000000 AM   19-SEP-24 06.00.36.084000000 AM
     1610   1698705135                 1 19-SEP-24 06.00.36.084000000 AM   19-SEP-24 07.00.38.853000000 AM
     1611   1698705135                 1 19-SEP-24 07.00.38.853000000 AM   19-SEP-24 08.00.41.663000000 AM
     1612   1698705135                 1 19-SEP-24 08.00.41.663000000 AM   19-SEP-24 09.00.44.288000000 AM

201 rows selected.

Each snapshot is assigned a unique snapshot ID, which appears in the SNAP_ID column. This column also exists in all  DBA_HIST_* tables and can be used to join with the DBA_HIST_SNAPSHOT view to retrieve the begin and end times of the interval the snapshot covers.

You can manually create snapshots to capture statistics at times different than those of the automatically generated snapshots using the following command:

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
END;
/

To view the current retention and interval settings for the AWR, use the DBA_HIST_WR_CONTROL view. Here is an example of how to use this view:

SQL> select snap_interval, retention from DBA_HIST_WR_CONTROL;
SNAP_INTERVAL         RETENTION
+00 01:00:00.000000   +08 00:00:00.000000

For a more readable output, you can use the following query:

SELECT 
    EXTRACT(DAY FROM snap_interval) * 24 * 60 +
    EXTRACT(HOUR FROM snap_interval) * 60 +
    EXTRACT(MINUTE FROM snap_interval) AS interval_in_minutes,
    EXTRACT(DAY FROM retention) || ':' ||
    EXTRACT(HOUR FROM retention) || ':' ||
    EXTRACT(MINUTE FROM retention) AS retention_day_hour_min
FROM 
    dba_hist_wr_control;

  INTERVAL_IN_MINUTES RETENTION_DAY_HOUR_MIN
                   60 8:0:0

In the preceding example, the snapshot interval is every hour (the default), and the retention is set for eight days ( also the default). Both the interval and retention period are customer-settable.

Modifying Snapshot Setting

In real-world scenarios, the default AWR retention period of eight days is often not long enough. For example, monthly processes that run once a month require comparing the current month’s execution behavior to previous month. The AWR retention period should at least cover a representative application work cycle. A longer retention period allows for a more extensive historical analysis of performance trends. In most cases, keeping a history of a couple of months is sufficient.

Another important factor influencing AWR data is the snapshot interval. If the interval is too long, the statistics become diluted. However, creating snapshots can noticeably impact database’s performance, and a very short interval may not yield meaningful insights. In most cases, setting the interval at half an hour strikes a good balance.

You can modify the snapshot collection interval using the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure. The following example sets the snapshot collection to occur every 30 minutes and retains snapshot data for 70 days (70 days * 24 hours * 60 minutes = 100800 minutes):

EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (
  retention => 100800,
  interval => 30
);

To verify the settings, you can use the following query:

SELECT retention, snap_interval FROM dba_hist_wr_control;

Output:

RETENTION             SNAP_INTERVAL
+70 00:00:00.000000   +00 00:30:00.000000

To reset the settings to their default values, you can execute the procedure without parameters:

EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS();

Snapshots capture top SQL statements if they are present in the cursor cache at the snapshot time. By default, snapshot only capture the top 30 SQL statements for each SQL criterion (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count). Although it is usually unnecessary, you can change this number using the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure. If you want to ensure a particular SQL statement is always captured, you can “color” it using:

EXEC DBMS_WORKLOAD_REPOSITORY.add_colored_sql('nnnnn');

The input parameter is the SQL ID. A colored SQL ID is captured in every snapshot, regardless of whether it is a top SQL. You can view the colored SQL statements by querying DBA_HIST_COLORED_SQL view:

select * from DBA_HIST_COLORED_SQL;

If needed, you can remove a colored SQL ID later using:

EXEC DBMS_WORKLOAD_REPOSITORY.remove_colored_sql('nnnnn');

Storage Consideration

Generally, the longer the retention period for snapshots, the more useful they become for performance analysis. Therefore, if space allows, set the retention period as long as possible. The size of a snapshot varies based on the database load, and by default, this data is stored in the SYSAUX tablespace. To accurately determine AWR space usage, you can run the awrinfo.sql script provided by Oracle. This script is located in the $ORACLE_HOME/rdbms/admin directory and provides information on the current space usage of the SYSAUX tablespace, as well as predictions for future usage trends. See the “Size estimates for AWR snapshots” section below for an example:

SQL> @$ORACLE_HOME/rdbms/admin/awrinfo.sql
This script will report general AWR information
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
...
(2) Size estimates for AWR snapshots                                                                          
*************************************                                                                         
|                                                                                                             
| Estimates based on 60 mins snapshot INTERVAL:                                                               
|    AWR size/day                        1,679.4 MB (71,654 K/snap * 24 snaps/day)                            
|    AWR size/wk                        11,755.7 MB (size_per_day * 7) per instance                           
|    AWR size/wk                        23,511.4 MB (size_per_day * 7) per database                           
|                                                                                                             
| Estimates based on 24 snaps in past 24 hours:                                                               
|    AWR size/day                        1,679.4 MB (71,654 K/snap and 24 snaps in past 24 hours)             
|    AWR size/wk                        11,755.7 MB (size_per_day * 7) per instance                           
|    AWR size/wk                        23,511.4 MB (size_per_day * 7) per database     
...

If there is space pressure on SYSAUX tablespace, you can manually drop snapshots using the DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANG procedure. When you drop snapshots, ASH data that belongs to specified snapshot range is also purged.

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
   low_snap_id IN NUMBER,
   high_snap_id IN NUMBER
   dbid IN NUMBER DEFAULT NULL);
-- Examples:
EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(37091, 37679);

If your database is tight in space and you need a longer retention period of snapshots, consider using an AWR warehouse. An AWR warehouse consolidates AWR data from one or more source databases. By uploading AWR data to a centralized AWR warehouse, you can free up space and improve performance on your production systems. The details of setting up and using an AWR warehouse are beyond are beyond the scope of this discussion.