Oracle AWR Report Generation Using awrrpt.sql Script

calendar Sep 23, 2025 / · 8 min read · AWR Performance Tuning dba_hist_snapshot dbms_workload_repository  ·
Share on: linkedin copy

Oracle AWR Report Generation Using awrrpt.sql Script

Purpose

Where awrddrpt.sql compares two time periods and awrsqrpt.sql looks at one SQL statement, awrrpt.sql gives the general view. It builds a report of the statistics held between two snapshot IDs on the local database instance. The report can be HTML or text.

The data comes from the Automatic Workload Repository, or AWR. AWR collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. A snapshot is one saved set of those statistics. A report between two snapshots shows what the instance did in that window.

A DBA runs this script when users report slowness at a known time. A DBA also runs it to record a baseline before a change, and again after the change. The steps below show the command, the prompts, the privileges, the related scripts and the common faults.

Code

 1-- 1. Find the snapshot IDs and their times
 2SELECT snap_id, begin_interval_time, end_interval_time
 3FROM   dba_hist_snapshot
 4ORDER  BY snap_id DESC
 5FETCH FIRST 10 ROWS ONLY;
 6
 7-- 2. Run the report script from SQL*Plus
 8@?/rdbms/admin/awrrpt.sql
 9
10-- 3. The same script with the full path
11@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Code Breakdown

The snapshot query

  • dba_hist_snapshot lists the snapshots that exist in the repository. Each row has a snap_id, a begin time and an end time. You need two of these IDs for the report.
  • ORDER BY snap_id DESC puts the newest snapshots first, so the recent window is easy to find.
  • FETCH FIRST 10 ROWS ONLY limits the output to ten rows. Raise the number if the incident was further back.

The script call

  • @ is the SQL*Plus command that runs a script file.
  • ? stands for the Oracle home directory when you run a script with @ or START. So the first call and the second call point to the same file.
  • rdbms/admin/awrrpt.sql is the path of the script below that directory.

The prompts

The script asks questions in this order:

  1. Report type. You choose an HTML report or a text report.
  2. Number of days. You give the number of days for which the script lists snapshot IDs. A value such as 2 lists the last two days.
  3. Beginning and ending snapshot ID. You give the two IDs that bound the window, for example 150 and 160.
  4. Report name. You type a name or accept the default.

The default name has the form awrrpt_<instance>_<begin>_<end>. In the Oracle example, accepting the default for instance 1 and snapshots 150 to 160 produced a report named awrrpt_1_150_160.

Key Points

  • The script reports the local instance. The report covers a range of snapshot IDs in the local database instance. To name another database or instance, use awrrpti.sql.
  • A license is required. The documentation says a license for Oracle Diagnostic Pack is required to use the AWR features it describes. Check your license before you give AWR reports to other teams.
  • The DBA role is the simple answer for privileges. The documentation says the DBA role is required to run these scripts. It also says any user can run an AWR report if that user has the ADVISOR and SELECT_CATALOG_ROLE roles and EXECUTE on dbms_workload_repository.
  • Snapshots must exist for your window. The report can only use snapshots that are still in the repository. In Oracle Database 23 the defaults are one snapshot every hour, kept for 8 days. Oracle 10g kept them for 7 days. Check the setting on your own database before you rely on either figure.
  • Do not turn off substitution. The prompts are SQLPlus substitution prompts. SET DEFINE OFF stops SQLPlus from scanning commands for substitution variables, so avoid it in the session that runs the script.
  • Prefer HTML for RAC. For an Oracle RAC environment, the documentation recommends an HTML report instead of a text report because it is much easier to read.

Insights and Best Practices

Pick the right script

Oracle supplies a family of report scripts. Each one answers a different question:

  • awrrpt.sql shows statistics from a range of snapshot IDs in the local database instance.
  • awrrpti.sql shows the same kind of report for a specific database instance. It lets you specify a database identifier and instance.
  • awrgrpt.sql shows statistics from a range of snapshot IDs using the current database instance in an Oracle RAC environment.
  • awrsqrpt.sql shows statistics of a particular SQL statement. The documentation says to run it to inspect or debug the performance of a SQL statement.
  • awrddrpt.sql compares detailed performance attributes and configuration settings between two selected time periods on the local database instance.

Start with awrrpt.sql when you do not yet know the cause. Move to awrsqrpt.sql once you have a SQL ID that looks suspect.

Choose the window with care

The best report covers the time of the problem and nothing else. A window of one snapshot interval gives a sharper picture than a window of a full day, because a long window averages the spike away. If the slowness lasted twenty minutes, use the two snapshots that surround it.

Also pick a second window from a time when the system was healthy. The documentation says that if the performance of a database degrades over time, AWR compare periods reports let you compare two periods to identify key differences that can help you diagnose the cause. That is the job of awrddrpt.sql.

Protect good snapshots with a baseline

A baseline is a set of snapshots from a specific time period that is preserved for comparison with other snapshots when a performance problem occurs. The snapshots in a baseline are excluded from the automatic AWR purging process and are retained indefinitely. If your normal peak is worth keeping, create a baseline for it before the retention period removes the snapshots. The dbms_workload_repository package is the one that manages baselines and snapshots.

Plan the retention to fit your cycle

As a rule of thumb, the retention period should capture at least one complete workload cycle. If your month-end batch is the heavy period, a short retention means you cannot compare this month with the last. Review the retention and snapshot interval with your team. Longer retention uses more space in the repository, so weigh the two.

Move AWR data to another database

When you do not want to analyze data on production, you can export it. The awrextr.sql script exports AWR data for a range of snapshots from the database into a Data Pump export file. You can transport that file to another database and import the data with awrload.sql. The documentation says you must be connected as the SYS user to run either script. It also says the export may take a while, depending on how much data you export.

When to Run This

  • A user or application owner reports slowness at a known time.
  • You need a record of workload before a patch, upgrade or schema change.
  • You need the same record after the change, to compare with the first.
  • You want to see which SQL statements and waits dominate a busy window.
  • You are asked to prove that a problem is, or is not, in the database.
  • You want a report on a copy of production, using exported AWR data.

Troubleshooting Common Issues

No snapshots are listed, or the one you need is missing. Run the dba_hist_snapshot query from the Code section. If the window you want is older than the retention period, the snapshots are gone. Automatic collection is only possible when the STATISTICS_LEVEL parameter is TYPICAL or ALL. Check that parameter if no new snapshots appear.

The script does not prompt, or the prompts show raw text. Check whether the session ran SET DEFINE OFF. As noted above, that setting controls whether SQL*Plus scans commands for substitution variables and replaces them with their values. Set it back to ON and run the script again.

The script cannot be found. The ? shortcut depends on the Oracle home directory being known to the client. Try the full path form, and check the ORACLE_HOME environment variable of your session.

You get a privilege error. Compare your account with the list in Key Points: the DBA role, or ADVISOR, SELECT_CATALOG_ROLE and EXECUTE on dbms_workload_repository.

You use RDS for Oracle and have no host access. The AWR scripts such as awrrpt.sql are installed on the database host server, and in that service you do not have direct access to the host. The service documentation recommends the procedures in rdsadmin.rdsadmin_diagnostic_util when you work with AWR. The service documentation also says AWR needs a license for the Diagnostics and Tuning packs, and that the CONTROL_MANAGEMENT_PACK_ACCESS parameter must be set to DIAGNOSTIC or DIAGNOSTIC+TUNING to enable it. The awr_report procedure takes the beginning and ending snapshot IDs and a report type, for example EXEC rdsadmin.rdsadmin_diagnostic_util.awr_report(101,106,'TEXT');.

The report opens but you do not know where to start. A practical order is to read the load profile first, then the top waits, then the top SQL by elapsed time. Fix the largest item and take a new report before you look at the next one.

References

Posts in this series