The 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. You can view it as a data warehouse for database statistics, metrics, and so on.
Every 60 minutes (by default) the database automatically captures
statistical information from the SGA and stores it in the AWR in the form of
snapshots. These snapshots are stored on the disk by a background process
called Manageability Monitor (MMON). By default, snapshots are retained for
eight days. You can modify both the snapshot interval and the retention
intervals.
The AWR contains hundreds of tables, all belonging to the SYSMAN schema
and stored in the SYSAUX tablespace. The Oracle database does not
support direct SQL access to the repository. Instead, use Enterprise Manager or
the DBMS_WORKLOAD_REPOSITORY package to work with the AWR.
AWR Infrastructure
The AWR
infrastructure has two major parts:
- An in-memory statistics collection
facility that is used by Oracle Database 11g components to
collect statistics. These statistics are stored in memory for performance
reasons. Statistics stored in memory are accessible through dynamic
performance (V$) views.
- The AWR snapshots that represent
the persistent portion of the facility. AWR snapshots are accessible
through data dictionary views and Enterprise Manager Database Control.
Statistics
are stored in persistent storage for several reasons:
- The statistics need to survive
instance crashes.
- Some analyses need historical data
for baseline comparisons.
- A memory overflow can occur. When
old statistics are replaced by new ones because of 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
the MMON background process. With the AWR, the Oracle database provides a way
to capture historical statistics data automatically without DBA intervention.
