Oracle AWR Report | Automatic Workload Repository

Introduction

The Automatic Workload Repository (AWR) is a fundamental component of Oracle Database that assists in performance tuning and monitoring. AWR collects and stores performance data critical for diagnosing and resolving performance bottlenecks in your database. In this article, we will explore how to effectively generate and analyze Oracle AWR reports to optimize your database performance.

What is the Automatic Workload Repository (AWR)?

The Automatic Workload Repository (AWR), introduced in Oracle Database 10g, is an integral part of every Oracle Database. It automatically captures and stores performance statistics, such as wait events, system activity, and object usage, that are crucial for identifying and troubleshooting database performance issues. By default, AWR snapshots are taken every hour and are retained for eight days, though these settings can be customized.

Key Features of Oracle AWR Report

  • Automatic Data Collection: AWR collects performance data automatically at specified intervals.
  • Historical Data Retention: Stores historical data for trend analysis and long-term performance management.
  • Comprehensive Metrics: Includes metrics on wait events, system activity, SQL statistics, and more.
  • Integration with Oracle Enterprise Manager (OEM): AWR data can be viewed and analyzed within OEM for enhanced usability.

How to Generate Oracle AWR Reports

Generating an Oracle AWR report is a straightforward process. Below are the steps to generate a report using SQL*Plus:

Step 1: Connect to the Database

Open SQL*Plus and connect to your database as a user with DBA privileges:

sqlplus / as sysdba

Step 2: Run the Oracle AWR Report Script

Execute the awrrpt.sql script located in the $ORACLE_HOME/rdbms/admin directory:

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

Step 3: Follow the Prompts

The script will prompt you for the report format (HTML or text), the snapshot IDs for the start and end of the reporting period, and the output file name. The output file will be generated in the $ORACLE_HOME/rdbms/admin directory.

Generating AWR Reports using SQL*Plus Script

The following SQL*Plus script allows you to generate both HTML and text-based AWR reports. The script requires the database ID (DBID), instance ID, begin snapshot ID, and end snapshot ID as arguments.

Find the Snapshot IDs

Before generating the AWR report, you need to identify the snapshot IDs for the period you are interested in. Run the following query to list available snapshots:


SQL> select
    snap_id,
    dbid,
    instance_number,
    begin_interval_time,
    end_interval_time
from
    dba_hist_snapshot
order by
    snap_id desc;

   SNAP_ID   DBID     INSTANCE_NUMBER      BEGIN_INTERVAL_TIME           END_INTERVAL_TIME
---------- ---------- --------------- ----------------------------- ----------------------------
         3 3358061257        1        01-JUN-24 04.47.23.000 AM      01-JUN-24 04.58.22.669 AM
         2 3358061257        1        01-JUN-24 03.58.37.000 AM      01-JUN-24 04.09.38.007 AM
         1 3358061257        1        31-MAY-24 11.15.18.000 AM      31-MAY-24 11.58.51.326 AM

SQL>

Execute the Script

Save the following SQL as awr_report.sql and use SQL*Plus to execute the script with the appropriate arguments (DBID, instance ID, begin snapshot ID, and end snapshot ID). For example:


--DBID: 3358061257
--instance ID:1
--begin snapshot ID:2
--end snapshot ID:3

sqlplus system@dbdocs @awr_report.sql 3358061257 1 2 3

Save the following SQL as "awr_report.sql"


set feedback off
set heading off
set linesize 10000
set pagesize 0
set termout off
set trimspool on
set verify off

define dbid=&1
define inst=&2
define bid=&3
define eid=&4

column awr_html_file new_value awr_html_file noprint
column awr_text_file new_value awr_text_file noprint

select 'awr_report_' || &&dbid || '_' || &&inst || '_' || &&bid || '_' || &&eid || '.html' awr_html_file from dual;
select 'awr_report_' || &&dbid || '_' || &&inst || '_' || &&bid || '_' || &&eid || '.txt' awr_text_file from dual;

set termout on
prompt Generating html AWR report
set termout off

spool &awr_html_file

select
    output
from
    table(
        dbms_workload_repository.awr_report_html(
            l_dbid       => &&dbid,
            l_inst_num   => &&inst,
            l_bid        => &&bid,
            l_eid        => &&eid
        )
    );

spool off

set termout on
prompt Generating text AWR report
set termout off

spool &awr_text_file

select
    output
from
    table(
        dbms_workload_repository.awr_report_text(
            l_dbid       => &&dbid,
            l_inst_num   => &&inst,
            l_bid        => &&bid,
            l_eid        => &&eid
        )
    );

spool off

quit

HTML and Text Reports

The script generates two files: an HTML report and a text report in the same location where the awr_report.sql file is located. The file names are in the format awr_report_dbid_inst_bid_eid.html and awr_report_dbid_inst_bid_eid.txt.

Conclusion

The Automatic Workload Repository (AWR) is an invaluable resource for monitoring and tuning Oracle Database performance. Regularly generating and analyzing Oracle AWR reports allows database administrators to proactively manage performance and address issues before they escalate. Whether using SQL*Plus or Oracle Enterprise Manager, leveraging AWR is crucial for maintaining a high-performing Oracle Database.


Related content



Rate Your Experience

: 89 : 1


Last updated in July, 2024

Online Tests
Read more

Cloud Technology
Read more

Oracle Database
Read more

MSSQL Database
Read more

PostGres Database
Read more

Linux
Read more

ASP/C#
Read more

Quick Access