Implementing Automatic Workload Repository (AWR)

Problem
You want to store historical database performance statistics on your database for tuning purposes.

Solution
By implementing and using the Automatic Workload Repository (AWR) within your database, Oracle will store interval-based historical statistics in your database for future reference. This information can be used to see what was going on within your database within a given period of time. By default, the AWR should be enabled within your database. The key initialization parameter to validate is the STATISTICS_LEVEL parameter:

SQL> show parameter statistics_level
NAME TYPE VALUE

---

statistics_level string TYPICAL

This parameter can be set to BASIC, TYPICAL (which is the default), and ALL. As long as the parameter is set to TYPICAL or ALL, statistics will be gathered for the AWR. If the parameter is set to BASIC, you simply need to modify the parameter in order to start gathering AWR statistics for your database:

alter system set statistics_level=TYPICAL scope=both;

How It Works
The predecessor of AWR, which is Statspack, requires manual setup and configuration to enable the statistics gathering. As stated, there generally is no setup required, unless the STATISTICS_LEVEL parameter has been changed to the BASIC setting. By default, an AWR snapshot is taken every hour on your database, and is stored, by default, for eight days. These are configurable settings that can be modified, if desired. See Recipe 4-2 for information on modifying the default settings of the AWR snapshots.
In addition to simply seeing the value of the STATISTICS_LEVEL parameter, you can also view the V$STATISTICS_LEVEL view to see this information, which has information on the STATISTICS_LEVEL setting, as well as all other relevant statistical components within your database:

SELECT statistics_name, activation_level, system_status
FROM v$statistics_level;

STATISTICS_NAME ACTIVAT SYSTEM_S

---

Buffer Cache Advice TYPICAL ENABLED
MTTR Advice TYPICAL ENABLED
Timed Statistics TYPICAL ENABLED
Timed OS Statistics ALL DISABLED
Segment Level Statistics TYPICAL ENABLED
PGA Advice TYPICAL ENABLED
Plan Execution Statistics ALL DISABLED
Shared Pool Advice TYPICAL ENABLED
Modification Monitoring TYPICAL ENABLED
Longops Statistics TYPICAL ENABLED
Bind Data Capture TYPICAL ENABLED
Ultrafast Latch Statistics TYPICAL ENABLED
Threshold-based Alerts TYPICAL ENABLED
Global Cache Statistics TYPICAL ENABLED
Active Session History TYPICAL ENABLED
Undo Advisor, Alerts and Fast Ramp up TYPICAL ENABLED
Streams Pool Advice TYPICAL ENABLED
Time Model Events TYPICAL ENABLED
Plan Execution Sampling TYPICAL ENABLED
Automated Maintenance Tasks TYPICAL ENABLED
SQL Monitoring TYPICAL ENABLED
Adaptive Thresholds Enabled TYPICAL ENABLED
V$IOSTAT_* statistics TYPICAL ENABLED
Session Wait Stack TYPICAL ENABLED
24 rows selected.

The type of information that is stored in the AWR includes the following:
• Statistics regarding object access and usage
• Time model statistics
• System statistics
• Session statistics
• SQL statements
The information gathered is then grouped and formatted by category. Some of the categories found on the report include the following:
• Instance efficiency
• Top 5 timed events
• Memory and CPU statistics
• Wait information
• SQL statement information
• Miscellaneous operating system and database statistics
• Database file and tablespace usage information

Note To use AWR functionality, the following must apply. First, you must be licensed for the Oracle Diagnostics Pack, otherwise you need to use Statspack. Second, the CONTROL_MANAGEMENT_PACK_ACCESS parameter must be set to DIAGNOSTIC+TUNING or DIAGNOSTIC.