Generate Oracle AWR Reports

System Management Center allows you to generate Automatic Workload Repository (AWR) reports for your environment.

What do you want to know?

• Why is my system running slow?

• Why is the query I submitted taking longer time than expected?

• How can I diagnose performance of an Oracle SQL statement?

Which Reports are Available?

To generate AWR reports:

1. In System Management Center, select and run system queries with the oracle - AWR keywords.

   Open

   

2. To customize how the report information is displayed, click the Print button drop down to print, or click the Export button drop down and select the file type to export query reports.

3. To return to the Queries workspace, click the Queries bottom tab.

Diagnose Performance of an Oracle SQL Statement

You can use the Oracle - AWR Top Consumers Pivot report to gather performance information about an individual Oracle SQL statement to help improve processing. Based on this information, the course of action may include any or all of the following: modification to an existing index, a new index, an Oracle Hint added to the SQL statement, a re‐write of the SQL statement, or a change to the underlying process or procedure.

In addition to the performance statistics, you may need to gather other information if sensitive queries or highly active tables are involved. You will also need to run at least one AWR report for the one-hour SNAPSHOT time period where the SQL query is prominent. If you copy the SQL statement in question to a TEST environment and modify, add an index, or change it, you can rerun the Oracle - AWR Top Consumers Pivot report to compare performance statistics between environments.

To diagnose performance of an Oracle SQL statement:

1. Under Queries, click to select the Oracle - AWR Top Consumers Pivot query and click Run.

2. In the query report, right click the appropriate SQL ID and select Show Report.

   Open

   

3. Review the SQL report for the SQL_ID you selected.

   Open

   

On this page