Automatic Workload Repository (AWR) in Oracle Database

Automatic Workload Repository (AWR) in Oracle Database collects, processes, and maintains performance statistics for problem detection and self-tuning. AWR snapshots provide valuable insights into the performance of the database, and generating AWR reports is a key part of performance tuning and troubleshooting.

Here's a comprehensive guide on how to generate and use AWR reports, including the scripts needed and an example of how to run them.

Generating AWR Reports

AWR reports can be generated using Oracle-provided scripts. These scripts are typically located in the $ORACLE_HOME/rdbms/admin directory and include:

  • awrrpt.sql for a standard AWR report.
  • awrrpti.sql for an AWR report in a specific instance in an Oracle RAC environment.
  • awrgrpt.sql for an AWR Global report in an Oracle RAC environment.
  • awrsqrpt.sql for an AWR SQL report focusing on a particular SQL statement.

Steps to Generate an AWR Report

  1. Connect to SQL*Plus as a privileged user (usually SYSDBA):

    sqlplus / as sysdba
  2. Run the AWR report script:

  3. Follow the prompts:

    • Enter the type of report: Text or HTML.
    • Enter the start and end snapshot IDs: You can find snapshot IDs by querying the DBA_HIST_SNAPSHOT view.
    • Enter the name of the report file: Specify a name and location for the output file.

Example of Running an AWR Report

1. Connect to SQL*Plus

sqlplus / as sysdba

2. Run the AWR Report Script

SQL> @?/rdbms/admin/awrrpt.sql

3. Follow the Prompts

You will be prompted to enter various details. Here’s an example session:

Specify the Report Type (text or html) [html]: html Type Specified: html Instances in this Workload Repository schema DB Id Inst Num DB Name Instance Host -------- -------- -------- ------------ ------------ 12345678 1 ORCL orcl host01 Using 12345678 for database Id Using 1 for instance number Specify the number of days of snapshots to choose from: Enter value for num_days: 1 Listing the last day's worth of snapshots Snap Instance DB Name Snap Id Snap Started Level ------------ -------- ------- --------------- ----- orcl ORCL 1000 01-Jul-24 00:00 1 1001 01-Jul-24 01:00 1 1002 01-Jul-24 02:00 1 ... 1024 01-Jul-24 23:00 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 1000 Begin Snapshot Id specified: 1000 Enter value for end_snap: 1024 End Snapshot Id specified: 1024 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrrpt_1_1000_1024.html. To use this name, press <return> to continue, otherwise enter an alternative. Enter value for report_name:

Analyzing the AWR Report

Once the report is generated, it will be saved to the specified file in HTML or text format. You can open this file with a web browser (for HTML) or a text editor (for text) to review the contents. The AWR report includes various sections such as:

  • Instance Information: Details about the database instance and host.
  • Load Profile: Summary of workload metrics.
  • Wait Events Statistics: The most significant events in terms of time consumption.
  • SQL Statistics: Information about the most resource-intensive SQL statements.
  • Instance Efficiency Percentages: Metrics that indicate the efficiency of instance operations.
  • Wait Events: Detailed wait event statistics.

Using AWR Report for Performance Tuning

  1. Identify Bottlenecks:

    • Review the "SQL ordered by Elapsed Time/CPU" section to identify the main performance bottlenecks.
    • Examine the wait events to understand what resources are being waited on the most.
  2. Analyze SQL Statements:

    • Focus on the SQL Statistics section to find the SQL statements consuming the most resources.
    • Look for SQL statements with high execution times, buffer gets, or disk reads.
  3. Instance Efficiency:

    • Check the Instance Efficiency Percentages to determine how well the instance is utilizing its resources.
    • Metrics like Buffer Cache Hit Ratio, Library Cache Hit Ratio, etc., provide insights into resource utilization.
  4. Review Resource Usage:

    • The "Load Profile" section gives an overview of resource usage over the snapshot period, including logical and physical reads, redo size, and more.
  5. Check for Configuration Issues:

    • Analyze sections related to memory usage, latch contention, and I/O statistics to identify potential configuration problems.


Please do like and subscribe to my youtube channel: If you like this post please follow,share and comment