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.
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.
Generating an Oracle AWR report is a straightforward process. Below are the steps to generate a report using SQL*Plus:
Open SQL*Plus and connect to your database as a user with DBA privileges:
sqlplus / as sysdba
Execute the awrrpt.sql script located in the $ORACLE_HOME/rdbms/admin directory:
awrrpt.sql
$ORACLE_HOME/rdbms/admin
SQL> @?/rdbms/admin/awrrpt.sql
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.
$ORACLE_HOME/rdbms/admin directory
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.
database ID (DBID), instance ID, begin snapshot ID, and end snapshot ID
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>
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:
awr_report.sql
--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
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.
awr_report_dbid_inst_bid_eid.html
awr_report_dbid_inst_bid_eid.txt
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.