AWR is the infrastructure

AWR is the infrastructure that provides services to Oracle Database 11g components to collect, maintain, and utilize statistics for problem detection and self-tuning purposes.

The AWR infrastructure consists of two major parts:

•An in-memory statistics collection facility that is used by various components to collect statistics. These statistics are stored in memory for performance reasons. Statistics stored in memory are accessible through dynamic performance (V$) views.

•AWR snapshots represent the persistent portion of the facility. The AWR snapshots are accessible through data dictionary (DBA) views and Database Control.

Statistics are stored in persistent storage for several reasons:

•The statistics need to survive instance crashes.

•Historical data for baseline comparisons is needed for certain types of analysis.

•Memory overflow: When old statistics are replaced by new ones due to memory shortage, the replaced data can be stored for later use.

The memory version of the statistics is transferred to disk on a regular basis by a background process called MMON (Manageability Monitor).

With AWR, the Oracle database server provides a way to capture historical statistics data automatically, without the intervention of DBAs.

Automatic Workload Repository Data

AWR captures a variety of statistics. AWR stores base statistics, that is, counters and value statistics (for example, log file switches and process memory allocated). AWR captures SQL statistics such as disk reads per SQL statement. Metrics such as physical reads per minute are also captured.

The Active Session History (ASH) data is captured first to memory at one-second intervals for only those sessions that are currently active (performing a database call). Then the ASH data is reduced by a factor of ten by storing to disk a random sample of the in-memory data. The ASH data is heavily used by Automatic Database Diagnostic Monitor (ADDM) to identify root causes of performance issues.

The advisor reports produced by ADDM, the Segment Advisor, and other advisors are also stored in AWR for later viewing.

The statistics exist at two levels: the in-memory recent statistics in V$ views, and persistent statistics that are stored on disk as snapshots and DBA_* views.

AWR Snapshot Purging Policy

You control the amount of historical AWR statistics by setting a retention period and a snapshot interval. In general, snapshots are removed automatically in chronological order. Snapshots that belong to baselines are retained until their baselines are removed or expire. On a typical system with 10 active sessions, AWR collections require 200 MB to 300 MB of space if the data is kept for seven days. The space consumption depends mainly on the number of active sessions in the system. A sizing script, utlsyxsz.sql, includes factors such as the size of the current occupants of the SYSAUX tablespace, number of active sessions, frequency of snapshots, and retention time. The awrinfo.sql script produces a report of the estimated growth rates of various occupants of the SYSAUX tablespace. Both scripts are located in the $ORACLE_HOME/rdbms/admin directory.

AWR handles space management for the snapshots. Every night the MMON process purges snapshots that are older than the retention period. If AWR detects that SYSAUX is out of space, it automatically reuses the space occupied by the oldest set of snapshots by deleting them. An alert is then sent to the DBA to indicate that SYSAUX is under space pressure.

Automatic Workload Repository: Overview

AWR is the infrastructure that provides services to Oracle Database 11g components to collect, maintain, and utilize statistics for problem detection and self-tuning purposes.

  The AWR infrastructure consists of two major parts:

An in-memory statistics collection facility that is used by various components to collect statistics. These statistics are stored in memory for performance reasons. Statistics stored in memory are accessible through dynamic performance (V$) views.

• AWR snapshots represent the persistent portion of the facility. The AWR snapshots are accessible through data dictionary (DBA) views and Database Control.

• The memory version of the statistics is transferred to disk on a regular basis by a background process called MMON (Manageability Monitor).

Important Links:

• ADDM and Database Time
• ADDM Performance Monitoring
• AWR Reports
• OS Statistics from AWR Reports