Managing Snapshots

By default, Oracle Database generates snapshots once every hour, and retains the statistics in the workload repository for 8 days. When necessary, you can manually create or drop snapshots and modify snapshot settings.

This section describes how to manage snapshots and contains the following topics:

See Also:

"Snapshots" for information about snapshots

User Interfaces for Managing Snapshots

The primary interface for managing snapshots is Oracle Enterprise Manager Cloud Control (Cloud Control). Whenever possible, you should manage snapshots using Cloud Control.

If Cloud Control is unavailable, then manage snapshots using the DBMS_WORKLOAD_REPOSITORY package in the command-line interface. The DBA role is required to invoke the DBMS_WORKLOAD_REPOSITORY procedures.

See Also:

Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_WORKLOAD_REPOSITORY package

Creating Snapshots

By default, Oracle Database automatically generates snapshots once every hour. However, you may want to manually create snapshots to capture statistics at times different from those of the automatically generated snapshots.

Creating Snapshots Using the Command-Line Interface

To manually create snapshots, use the CREATE_SNAPSHOT procedure. The following example shows a CREATE_SNAPSHOT procedure call. 

BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/

In this example, a snapshot is created immediately on the local database instance. To view information about an existing snapshot, use the DBA_HIST_SNAPSHOT view.

Note:

You can specify value for the flush_level parameter of the CREATE_SNAPSHOT procedure to either TYPICAL or ALL. The default value for the flush level is TYPICAL.

The flush level signifies the breadth and depth of the AWR statistics to be captured. If you want to capture all the AWR statistics, then set the flush level to ALL. If you want to skip few AWR statistics, such as, SQL statistics, segment statistics, and files and tablespace statistics for performance reasons, then set the flush level to TYPICAL.

See Also:

Dropping Snapshots

By default, Oracle Database automatically purges snapshots that have been stored in AWR for over 8 days. However, you may want to manually drop a range of snapshots to free up space.

Dropping Snapshots Using the Command-Line Interface

To manually drop a range of snapshots, use the DROP_SNAPSHOT_RANGE procedure. The following example shows a DROP_SNAPSHOT_RANGE procedure call. 

BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id  => 22, 
                                                high_snap_id => 32, 
                                                dbid         => 3310949047);
END;
/

In the example, snapshots with snapshot IDs ranging from 22 to 32 are dropped immediately from the database instance with the database identifier of 3310949047. Any ASH data that were captured during this snapshot range are also purged.

Tip:

To determine which snapshots to drop, use the DBA_HIST_SNAPSHOT view to review the existing snapshots

See Also:

Modifying Snapshot Settings

You can adjust the interval, retention period, and number of top SQL to flush for snapshot generation, but note that this can affect the precision of the Oracle Database diagnostic tools.

Modifying Snapshot Settings Using the Command-Line Interface

You can modify snapshot settings using the following parameters of the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure:

Parameter Description

INTERVAL

This setting affects how often the database automatically generates snapshots.

RETENTION

This setting affects how long the database stores snapshots in AWR.

TOPNSQL

This setting affects the number of top SQL to flush for each SQL criteria (elapsed time, CPU time, parse calls, sharable memory, and version count).

This setting is not affected by the statistics/flush level and overrides the system default behavior for AWR SQL collection. It is possible to set the value for this setting to MAXIMUM to capture the complete set of SQL in the shared SQL area, though doing so (or by setting the value to a very high number) may lead to possible space and performance issues because there will be more data to collect and store.

DBID

This setting specifies the database identifier whose settings will be modified by this procedure. If NULL, it will modify the DBID of the current database.

TABLESPACE_NAME

This setting affects where the database stores snapshots.

AUTO_PURGE

This setting affects whether automatic purge should be performed.

The following example shows how to modify snapshot settings using the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure: 

BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention  => 43200, 
                                                     interval   => 30, 
                                                     topnsql    => 100, 
                                                     dbid       => 3310949047);
END;
/

In this example, snapshot settings for the database with the database identifier of 3310949047 are modified as follows:

  • The retention period is specified as 43200 minutes (30 days).

  • The interval between each snapshot is specified as 30 minutes.

  • The number of top SQL to flush for each SQL criteria is specified as 100.

To get information about the current snapshot settings for your database, use the DBA_HIST_WR_CONTROL view as shown in the following example: 

SQL> select snap_interval, retention, tablespace_name from DBA_HIST_WR_CONTROL; 

SNAP_INTERVAL               RETENTION         TABLESPACE_NAME
------------------------------------------------------------- 
+00000 01:00:00.0        +00008 00:00:00.0    SYSAUX

The snap_interval and retention values are displayed in the format: 

+[days] [hours]:[minutes]:[seconds].[nanoseconds]

See Also: