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  ·
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 a SQL*Plus substitution variable that automatically represents 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.[^7][^2][^8][^1]

Prerequisites

Before executing the AWR report script, several requirements must be met. The database user must have SELECT_CATALOG_ROLE privileges to access the AWR repository views. An active Oracle Diagnostic Pack license is required since AWR is part of this licensed feature set. The database version must be Oracle 10g or higher, as AWR was introduced in Oracle Database 10g. 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 automatically detects the current database instance and calls the underlying awrrpti.sql script for interactive snapshot selection. 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 file is generated in the current directory and 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 global reports showing performance across all nodes. 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 (?) serves as a substitution variable representing the ORACLE_HOME environment variable, automatically 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

If the script prompts for unexpected substitution variables, the ampersand (&) character in comments or strings may be causing issues; use SET DEFINE OFF; before running the script to disable substitution variable prompting. 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