Working with Automatic Workload Repository Performance Snapshots

AWR and Statspack Snapshots
For those readers that are familiar with Statspack, you can loosely equate a snapshot stored in the AWR repository to its Statspack predecessor. Statspack is a set of SQL, PL/SQL and SQL*PLUS scripts that collects, stores and reports on Oracle database performance statistics. The utility stores the statistical information in permanent tables, which allows the data to be used for after-the-fact performance analysis as well as performance trending. The administrator collects performance information by running data captures called Statspack Snapshots. Snapshots record key statistics, high resource consuming SQL statements and wait events. To report on this information, users run an Oracle supplied script that prompts them for beginning and ending snapshot IDs. Oracle then displays performance information, which can be spooled to an output file in SQLPLUS. Since the information is stored in permanent tables, the information is available to be viewed at any time.

Oracle 10G's statistics capturing mechanism follows the same snapshot premise. Oracle 10G utilizes a new background process called MMON to capture key performance statistics every 60 minutes by default and stores that information in the Automatic Workload Repository (you'll need to remember that). The MMON snapshot mechanism can be loosely compared to its Statspack predecessor.

The major differences between Statspack and AWR snapshots are:

• Statspack snapshots must be run by an external scheduler (dbms_jobs, CRON, etc.). AWR snapshots are scheduled every 60 minutes by default. A new background server process called MMON is responsible for initiating the snapshot mechanism. Administrators can manually adjust the snapshot interval if so desired. I'll show you how to adjust the interval in my next blog.
• ADDM captures a much greater depth and breadth of statistics than Statspack does. During snapshot processing, MMON transfers an in-memory version of the statistics to the permanent statistics tables.
• Statspack snapshot purges must be scheduled manually. When the Statspack tablespace runs out of space, Statspack quits working. AWR snapshots are purged automatically by MMON every night. MMON, by default, tries to keep one week's worth of AWR snapshots available. If AWR detects that the SYSAUX tablespace is in danger of running out of space, it will free space in SYSAUX by automatically deleting the oldest set of snapshots. If this occurs, AWR will initiate a server-generated alert to notify administrators of the out-of-space error condition. Administrators can manually adjust the amount of information retained by invoking the MODIFY_SNAPSHOT_SETTINGS PL/SQL stored procedure and specifying the RETENTION parameter input variable.

AWR Repository
We previously learned that MMON captures key performance statistics and stores them in the Automatic Workload Repository. Oracle describes the Automatic Workload Repository as the data warehouse of the Oracle10g database. It is used as the statistics data source for all other components of the Common Manageability Infrastructure (refer to figure 1).

AWR Snapshots
AWR snapshots provide a persistent view of database statistics. They are stored in the system-defined WR schema, which resides in a new tablespace called SYSAUX. A snapshot is a collection of performance statistics that are captured at a specific point in time. The snapshot data points are used to compute the rate of change for the statistic being measured. A unique SNAP_ID snapshot identifier identifies each snapshot.

Storing statistics generated by AWR snapshots in the database allows administrators to analyze problems that occurred in the past. This historical information will help administrators finally answer questions like, "My program ran long two days ago; can you fix it?"

The stored statistics also allow the database to compare its current performance to a stored baseline. The database is then able to initiate its own early-warning performance alerts when baseline measurements are exceeded.

The Automatic Database Diagnostic Monitor (discussed in an earlier blog) uses the snapshot information to automatically identify performance problems and make recommendations to correct them. In addition, administrators are able to use the historical data to create performance reports (discussed in an upcoming blog) and perform performance trending analysis.

Metrics vs. Base Statistics
Base statistics are the raw data points collected by AWR. An example of a base statistic would be the number of reads per second performed on a particular database data object. Metrics are computed statistics derived from base statistics. The MMON server process uses the base statistics as input to calculate their corresponding metrics. These computed statistics are used by internal components for system health monitoring, early warning problem detection, and self-tuning. An example of a metric would be the average number of physical reads per second performed on a database data object within the last 60-minute time period. Pre-computing metrics allows internal components to quickly compute the rate of change of system statistics.

In the past, administrators who wanted to determine the impact a specific workload had on the database used Statspack to capture baseline statistics before the run, ran the workload being evaluated, ran Statspack again to capture statistics after the run, then manually computed the rate of change for the particular base statistic. With pre-computed metrics, administrators only need to run the workload, then select the desired metric values from the new V$ metric tables.

AWR Snapshot Statistics Levels
Administrators are able to control the breadth and depth of statistics to capture by setting the STATISTICS_LEVEL initialization parameter. The parameter has three different possible values:

• Basic - The collection of AWR statistics and computation of metrics is deactivated.
• Typical (default value) - Partial statistics are collected. AWR collects only those statistics required to provide base-level monitoring capabilities. This is the setting currently recommended by Oracle.
• All - All possible statistics are captured. The additional statistics are used for manual SQL diagnosis. The ALL setting does incur additional overhead.

AWR Snapshot Baselines
Administrators create snapshot baselines to capture and measure performance statistics for a particular time period. The baseline data can then be used to compare current system performance against a time period when database performance was determined to be optimal. Threshold-based alerts can be set up to notify administrators when current system performance deviates from the statistics and metrics stored in the baseline snapshot. The baseline snapshot is defined on a pair of SNAP_IDs. The CREATE_BASELINE stored procedure accepts the START_SNAP_ID and END_SNAP_ID variables as input to create the snapshot baseline. Administrators use the BASELINE_NAME variable to uniquely identify the baseline snapshot. AWR does not delete snapshots defined as baseline snapshots.

AWR Snapshot Comparison
10G EM also allows two sets of snapshots to be compared to one another. The results of the comparison are displayed graphically using horizontal bars to indicate differences between the two measurements. It is important to note that 10G EM does not compare the hundreds of statistics stored in an AWR snapshot. Oracle has preselected key statistics to evaluate which makes the output much easier to review. Since Oracle has preselected the statistics, it is safe to assume that it views them to be the most critical.

Conclusion
Now that we have a firm understanding of the AWR snapshot environment, we'll be ready to learn how to tailor it to meet our specific requirements and use its contents to allow us to achieve "Database Performance Maximus".
Thanks for reading.