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:


    @?/rdbms/admin/awrrpt.sql

  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: https://www.youtube.com/@foalabs If you like this post please follow,share and comment