AWR Snapshot Settings

With the MODIFY_SNAPSHOT_SETTINGS procedure, you can control the snapshot
parameters. You can use this procedure to change:
• The retention period. RETENTION is specified in minutes. The default is eight days; the
minimum is one day. Setting RETENTION to the value 0 disables automatic purging.
• The INTERVAL between snapshots. The minimum value is 10 minutes, the maximum is
100 years, and the default value is 60 minutes.
• The number of Top SQL statements for which to capture performance data. You are
allowed to specify the following values: DEFAULT, MAXIMUM, n, where n is the number of
Top SQL statements to flush for each SQL criteria such as Elapsed Time and CPU Time.
Specify DEFAULT to capture the Top 30 for level TYPICAL and Top 100 for level ALL of
STATISTICS_LEVEL. Specify MAXIMUM to capture the complete set of SQL in the
cursor cache. Specify NULL to keep the current setting.
Note: Under exceptional circumstances, automatic snapshot collection can be completely turned
off by setting the snapshot interval to 0. The automatic collection of the workload and statistical
data is stopped and much of the Oracle self-management functionality is not operational. In
addition, you are unable to manually create snapshots. For this reason, Oracle Corporation
strongly recommends that you do not turn off automatic snapshot collection.
Create a snapshot
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (};
Drop one or more snapshots
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
LOW_SNAP_ID => 102,
HIGH_SNAP_ID => 105);