Automatic Workload Repository (AWR) automatically collects and stores database performance statistics relating to problem detection and tuning, and it lies at the heart of the new database self-tuning mechanisms. The AWR was designed by Oracle as a replacement for the traditional STATSPACK utility, which helps you gather database performance statistics (the STATSPACK utility is still available, but Oracle strongly recommends using the AWR instead).
The AWR generates snapshots of key performance data, such as system and session statistics, segment-usage statistics, time-model statistics, and high-load SQL statistics, and it stores the snapshots in the SYSAUX tablespace. By default, the database will generate a performance snapshot every hour.
The AWR facility collects a large number of performance statistics, including the following:
Base statistics that are also part of the V$SYSSTAT and V$SESSSTAT views
SQL statistics that aid in the identification of resource-intensive SQL statements
Database object-usage statistics that inform you about how the database is currently accessing various objects.
Time-model statistics, which tell you how much time each database activity is taking
Wait statistics, which provide information about session waits
ASH statistics, which are flushed to the AWR on a regular basis
Database feature-usage statistics
Results of various management advisory sessions, such as the Segment Advisor and the SQL Access Advisor
Operating system statistics such as disk I/O and memory usage within the database
Single-instance DB:
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
Comments