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