LinkedIn pixel

by Vikram Kotwani – Principal Architect

Oracle Automatic Workload Repository (AWR)

AWR is a monitoring tool that collects, analyzes, and stores performance statistics, helping database administrators identify and resolve performance problems. When tuning a database, each component has its own unique set of statistics. To provide a common scale for comparison, database performance statistics are reported in a database-timed model. This model offers details on how much time is spent on performing database activities.

Running Oracle AWR Reports

One can generate the AWR report either through Oracle Enterprise Manager or via command line.

Running AWR from Command Line

To run AWR from the command line on the server where the database is installed, go to $ORACLE_HOME/rdbms/admin

Login to database:

sqlplus “/as sysdba”

@./awrrpt.sql

Type in HTML or txt for report type

Enter the value for num_days, for the number of days you want to see AWR snapshots.

Enter the start and end snapshots, enter filename, and select enter.

Running AWR from Oracle Enterprise Manager

Access the AWR Report by navigating to Performance -> AWR -> AWR Report on the database page.

P1_AWR_Automatic Workload Repository

 

Click the search button, choose the Begin and End snapshots, then select Generate Report. Once the report is generated, click the Save to File button to save the report to your local drive.

P2_AWR

 

Reading AWR Reports

The AWR has multiple sections that cover various database activities. To find what you need quickly, you may want to navigate directly to the appropriate section. Important sections in the AWR are discussed below:

Top Timed Events

The Top Timed Events section provides an overview of CPU and I/O constraints and displays where the most time is spent waiting for resources. Further breakdowns are available in the Foreground and Background Events sections.

P3_AWR

 

System I/O Statistics

In the System I/O Statistics section, you can view the overall system statistics and confirm if the I/O subsystem can handle the I/O demands made by the database. You can also verify the redo log switches and adjust the size of the redo logs to ensure that there are 3 to 4 switches per hour.

P4_AWR

 

SQL CPU & I/O Consumption

In this section, you can find details about the SQLs that were executed during the AWR interval. This section is important because it helps identify which SQLs were using the most system resources, leading to performance degradation. The SQLs with high consumption of CPU, I/O, or unoptimized reads are good candidates for tuning.

P5_AWR

P6_AWR

 

Segment Statistics

The Segment Statistics section provides a list of the most active segments. You can review this list to determine if any of the segments could benefit from a reorg or purpose of data, etc.

P7_AWR

 

Active Session History (ASH)

The ASH section provides information about active sessions and other relevant information. The following information can be found within the ASH sections:

SQL With Top Events & Row Sources

This section lists information about the relevant SQLs executing full table scans and waits events.

P8_AWR

 

Top Sessions

This section shows which user sessions consume the most resources.

P9_AWR

 

Automatic Database Diagnostic Monitor (ADDM)

ADDM is a diagnostic tool that analyzes the data in the Automatic Workload Repository (AWR) and provides recommendations for resolving issues found. ADDM often suggests utilizing Tuning Advisor to improve the execution of certain SQL. The recommended next steps would be to execute SQL tuning advisors on the identified SQL and review the various recommendations provided.

P10_AWR

Have Questions about Oracle AWR Report Best Practices?

If you find yourself with questions or in need of clarification regarding the best practices for AWR Reports, please contact our expert team or the author directly to learn more.

Vikram Kotwani – Principal Architect  

LinkedIn: https://www.linkedin.com/in/vikramkotwani/