The Automatic Workload Repository (AWR) is a collection of metadata that provides statistics on the various components of an Oracle database’s workload. It is the primary source of performance data for Oracle databases, and it can be used to identify performance bottlenecks and track their impact over time. The AWR collects information on all aspects of the database server, including system statistics, operating system events, memory usage, CPU usage, I/O usage, and SQL statements.
The main components of the AWR are:
1. Snapshots: The AWR takes regular snapshots of the database activity, which stores the contents of the statistics of the database at that specific time. By default, a new snapshot is taken every hour, or every 15 minutes in case of high load. This information is stored in the database’s data dictionary and provides a detailed picture of the database’s resource usage over time.
2. Baselines: A baseline serves as a reference point to see how performance changes over time. DBAs can manually create baselines and compare the existing performance data to those baselines. A baseline creation can be a point-in-time creation, where it is created manually before and after the major changes to the database. Also, a repeating baseline can be created to capture the performance metrics at a given interval.
3. Reports: Reports provide both high-level and detailed views of the AWR data. AWR reports help in identifying the cause of the problem by making them easy to navigate, consume and understand. Oracle provides several tools to generate reports, including the AWR Report, ADDM (Automatic Database Diagnostic Monitor), and ASH Analytics reports.
The AWR helps to identify performance issues in a few ways:
1. Performance trend analysis: The data collected by the AWR allows a DBA to identify trends in the performance of the database performance. If performance has degraded over time, it may indicate that the database is not correctly sized, or application workload has been increased.
2. Identification of most time-consuming operations: The Oracle AWR report ranks the most time-consuming SQL queries so that DBAs can tune them to improve performance. The ranking includes details such as the total time spent, waits, reads, and executions.
3. Identification of problems: The AWR periodically runs ADDM [Automatic Diagnostic/Diagnosis Monitor], which analyzes the workload and provides feedback on the actions that caused performance problems.
In conclusion, the AWR is a critical tool for performance monitoring and troubleshooting in Oracle Database systems. It provides detailed information on database usage and performance, and allows for trend analysis and baseline comparisons to identify and resolve performance issues.