you're reading...
Administration, Database, Oracle

Automatic Workload Repository Snapshots

Oracle Automatic Workload Repository (AWR) collects, processes and maintains performance statistics for problem detection and self-tuning purposes. The gathered data can be viewed in both reports and views. AWR collects cumulative and differential statistics data into AWR snapshots. The differential values captured by the snapshot represent the changes for each statistic over the time period of snapshot capture interval.

By default Oracle Database automatically generates AWR snapshots every hour and retains them in the workload repository for 8 days. Both capture interval and retention time period can be changed by DBA. A snapshot can also be created manually by DBA.

Check Exiting Snapshot

The already collected snapshots can be found in DBA_HIST_SNAPSHOT view.


Check Current Settings of AWR Snapshot

The current snapshot interval and retention time period are kept in DBA_HIST_WR_CONTROL view.


DBID               SNAP_INTERVAL        RETENTION               TOPNSQL
———-             ——————–            ——————–            ——————————
2345607244    +00000 00:30:00.0    +00030 00:00:00.0      DEFAULT
2433810523    +00000 00:30:00.0    +00030 00:00:00.0      DEFAULT

Typically you only get one row from above query. The results here have two entries because the database was cloned from another database.

Change Snapshot Settings

Snapshot capture interval, retention period and number of Top SQL for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, Shared Memory, and Version Count) can be modified for snapshot generation. To change the settings, use DBMS_WORKLOAD.MODIFY_SNAPSHOT_SETTING procedure.

interval => 30, topnsql => null, dbid => 2433810523);

retention New retention time (in minutes). The specified value must be in the range of MIN_RETENTION (1 day) to MAX_RETENTION (100 years). If ZERO is specified, snapshots will be retained forever. In that case a large system-defined value will be used as the retention setting. If NULL is specified, the current value for retention is preserved.

NOTE: The retention setting must be greater than or equal to the window size of the ‘SYSTEM_MOVING_WINDOW’ baseline. If the retention needs to be less than the window size, the MODIFY_BASELINE_WINDOW_SIZE Procedure can be used to adjust the window size.

interval New interval setting between each snapshot, in units of minutes. The specified value must be in the range MIN_INTERVAL (10 minutes) to MAX_INTERVAL (1 year). If ZERO is specified, automatic and manual snapshots will be disabled. If NULL is specified, the current value is preserved.

topnsql If NUMBER: Top N SQL size. The number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count). The value for this setting will not be affected by the statistics/flush level and will override the system default behavior for the AWR SQL collection. The setting will have a minimum value of 30 and a maximum value of 50,000. Specifying NULL will keep the current setting. If VARCHAR2: Users are allowed to specify the following values: (DEFAULT, MAXIMUM, N), where N is the number of  Top SQL to flush for each SQL criteria. Specifying DEFAULT will revert the system back to the default behavior of Top 30 for statistics level TYPICAL and Top 100 for statistics level ALL. Specifying MAXIMUM will cause the system to capture the complete set of SQL in the cursor cache. Specifying the number N is equivalent to setting the Top N SQL with the NUMBER type. Specifying NULL for this argument will keep the current setting.

dbid Database identifier in AWR for which to modify the snapshot settings. If NULL is specified, the local dbid will be used. It defaults to NULL.

Manually Create Snapshot

You can manually create a snapshot at any time by calling dbms_workload.create_snapshot() procedure.


Snapshot Collection Problem

For various reasons, snapshot collection sometimes stops working. In our environment a test database is periodically refreshed from the physical standby database of the production. AWR snapshot collection stops each time after refreshing/cloning.

In MMON trace file we see following error message.

*** 2013-01-02 14:03:38.475
Unable to schedule a MMON slave at: Auto Flush Main 1
Slave action has been temporarily suspended
– Slave action had prior policy violations.
Unknown return code: 101

Normally you would not know the problem until you need to generate AWR reports or to review ADDM. At that time you find bunch of AWR snapshots are missing. To detect the problem you can query DBA_HIST_SNAPSHOT for timestamps of existing snapshots and compare them with the settings from DBA_HIST_WR_CONTROL view. In our environment we have a cron job running to check AWR snapshots are taken as set.

To fix the problem first I need to kill the database’s MMON process. It will start again by the database automatically. Then I manually create a AWR snapshot. After that Oracle will automatically create AWR snapshots based on your settings.



About Hong Wang

I am an Oracle DBA, working in Oracle database since version 7.3. Worked in both application development and production support. Many experiences in real world complicate problems and database projects. This blog serves as a collections of notes I write on my database studies as well as issues I encountered/solved. Your comments are well welcome.


No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: