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.
select BEGIN_INTERVAL_TIME, END_INTERVAL_TIME, FLUSH_ELAPSED, ERROR_COUNT, SNAP_FLAG
order by END_INTERVAL_TIME;
Check Current Settings of AWR Snapshot
The current snapshot interval and retention time period are kept in DBA_HIST_WR_CONTROL view.
select DBID, SNAP_INTERVAL, RETENTION, TOPNSQL
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.
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200,
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.