Oracle AWR Report Generation Using awrrpt.sql Script

calendar Sep 23, 2025 / · 6 min read · Oracle AWR Performance Tuning Database Administration SQL Scripts Oracle Diagnostics Database Monitoring RDBMS dba_hist_snapshot  ·
Share on: linkedin copy

Oracle AWR Report Generation Using awrrpt.sql Script

The awrrpt.sql script is a powerful Oracle tool that generates comprehensive performance reports from the Automatic Workload Repository (AWR), providing database administrators with detailed insights into database performance over specific time periods. This script is essential for troubleshooting performance issues, identifying bottlenecks, and optimizing Oracle database operations.

What is the Command

1@?/rdbms/admin/awrrpt.sql

This command executes the AWR report generation script located in the Oracle RDBMS admin directory. The question mark (?) is SQL*Plus shorthand for the ORACLE_HOME directory path, eliminating the need to type the full path.

Purpose

The awrrpt.sql script serves as Oracle's primary tool for generating Automatic Workload Repository reports that display database performance statistics between two snapshot IDs. AWR collects, processes, and maintains performance statistics for problem detection and self-tuning purposes, making it indispensable for Oracle performance tuning professionals. Database administrators use these reports to analyze SQL execution patterns, identify resource-intensive queries, examine wait events, and evaluate overall database health during specific time intervals.

Prerequisites

Before executing the AWR report script, several requirements must be met. Oracle documents the DBA role as required to run these scripts; a non-DBA user needs the ADVISOR privilege, SELECT_CATALOG_ROLE, and EXECUTE on dbms_workload_repository. An active Oracle Diagnostic Pack license is required since AWR is part of this licensed feature set. Additionally, AWR snapshots must already exist in the repository for the time period being analyzed.

How It Works

The awrrpt.sql script operates through an interactive command-line interface that guides users through the report generation process. When executed, the script uses the current (local) database instance and prompts for the snapshot range. The process involves several user prompts where administrators specify report preferences and parameters.

Interactive Prompts

The script first prompts for the report format, allowing selection between HTML (recommended for readability) or text format (suitable for automation). Next, it requests the number of days to display available snapshots, such as entering "2" to see snapshots from the last two days. The script then displays a list of existing snapshots with their IDs and timestamps. Users specify beginning and ending snapshot IDs to define the analysis period, for example, snapshot 150 to 160. Finally, the script requests a report name or accepts the default naming convention of awrrpt_instancenumber_beginsnap_endsnap.

Execution Steps

To generate an AWR report on the local database instance, connect to SQL*Plus with appropriate privileges and execute the command @$ORACLE_HOME/rdbms/admin/awrrpt.sql or use the shorter form @?/rdbms/admin/awrrpt.sql. Follow the interactive prompts to select report type, specify the snapshot range, and provide a report name. The report is written to a file that can be viewed in a web browser if HTML format was selected.

Key Features

The script provides automatic instance detection by defaulting to the current connected database instance. It supports both interactive mode for manual report generation and non-interactive mode for automation using predefined parameters. The comprehensive reporting capabilities are based on Oracle's proven Statspack functionality with significant enhancements. Reports can be generated in either HTML format for enhanced readability with charts and tables, or text format for scripting and automation.

Oracle provides several variations of the AWR report script for different scenarios. The awrrpti.sql script generates reports for a specific database instance by prompting for database ID and instance number, useful in RAC environments. For RAC databases, awrgrpt.sql produces a report for an Oracle RAC environment. The awrsqrpt.sql script focuses specifically on SQL statement performance analysis. To compare performance between two time periods, awrddrpt.sql generates difference reports. For exporting and importing AWR data between databases, use awrextr.sql and awrload.sql respectively.

Syntax Explanation

The @ symbol is the SQL*Plus command to execute a script file from a specified location. The question mark (?) is shorthand for the ORACLE_HOME directory, resolving to the Oracle installation directory path. The /rdbms/admin/ portion specifies the subdirectory within ORACLE_HOME where Oracle administrative scripts are stored. Finally, awrrpt.sql is the actual script filename that contains the AWR report generation code.

Performance Considerations

AWR report generation can be resource-intensive and should be run during low-activity periods on production systems to minimize impact. Consider establishing AWR baselines for consistent performance comparisons over time. The report generation process queries numerous AWR repository views and may take several minutes depending on the snapshot range and database size. For cloud-based Oracle databases like RDS, alternative methods using the rdsadmin_diagnostic_util package may be more appropriate.

Common Use Cases

Database administrators generate AWR reports when investigating performance degradation or slowdowns during specific time periods. The reports help identify resource-intensive SQL statements that consume excessive CPU, memory, or I/O resources. AWR reports support capacity planning by analyzing historical workload patterns and growth trends. They provide baseline performance metrics for comparison before and after database changes. During application performance testing, AWR reports validate that database operations meet performance requirements.

Troubleshooting Tips

The script's prompts (such as Enter value for report_type) are SQLPlus substitution-variable prompts, so do not run it with SET DEFINE OFF, which stops SQLPlus from scanning commands for substitution variables. Ensure AWR snapshots exist for the desired time period by querying DBA_HIST_SNAPSHOT view. Verify that the Oracle Diagnostic Pack license is properly configured, as AWR is a licensed feature. If running on RAC databases, use the appropriate RAC-specific scripts like awrgrpt.sql or awrrpti.sql instead of the basic awrrpt.sql. Check that ORACLE_HOME environment variable is correctly set if the ? substitution does not resolve properly.

Best Practices

Generate AWR reports covering the time period when performance issues occurred rather than arbitrary timeframes. Compare reports from good performance periods with poor performance periods using difference reports. Save generated AWR reports with meaningful names that include dates and incident identifiers for future reference. Automate regular AWR report generation for baseline documentation using scheduler jobs. Review AWR reports systematically starting with the load profile, top SQL statements, wait events, and then detailed sections. Focus on the top time-consuming SQL statements and significant wait events first before investigating less impactful areas.

References

Posts in this series