How to Generate an Oracle AWR Report
The Automatic Workload Repository (AWR) is Oracle's built-in performance warehouse. Every 60 minutes — by default — the database takes a snapshot of hundreds of cumulative statistics: wait events, SQL execution plans, system load, segment activity, memory usage, and more. These snapshots are stored in the SYSAUX tablespace for 8 days by default.
An AWR report is a diff between two snapshots. It tells you exactly what the database did during that window: which SQL statements consumed the most resources, which wait events dominated, how much I/O occurred, and how the load compared to the snapshot interval before it.
If you're troubleshooting a slow database, the AWR report is almost always the first artifact you should collect. This guide walks you through the entire process, from finding the right snapshots to reading the output.
Licensing note: AWR requires the Oracle Diagnostics Pack, which is an add-on to Enterprise Edition. If you are not licensed for it, use Statspack instead — it's free, ships with every Oracle edition, and provides similar information. See the Statspack section at the end of this article.
When Should You Generate an AWR Report?
Choosing the right time window is more important than the mechanics of running the script. Generate an AWR report:
- During or immediately after a performance incident — bracket the slow period as tightly as possible with your snapshot selection.
- For a representative peak load window — for example, the busiest hour of your batch processing, or month-end close.
- Before and after a change — to compare performance before and after an index, a patch, or a configuration change.
What to avoid:
- Never span an instance restart. AWR statistics are cumulative and reset on shutdown. A report that crosses a bounce will show meaningless deltas.
- Avoid windows that are too wide. A 24-hour report will average your 15-minute spike into noise. Aim for the shortest window that still contains the problem — typically 30 to 60 minutes.
- Avoid very short windows. Reports under 15 minutes may not capture enough activity to be statistically meaningful.
Step 1: Find the Snapshot IDs
Connect with a user that has SELECT access to the AWR views (any user with DBA role, or a user granted SELECT_CATALOG_ROLE) and query recent snapshots:
SELECT snap_id,
TO_CHAR(begin_interval_time, 'YYYY-MM-DD HH24:MI') AS begin_time,
TO_CHAR(end_interval_time, 'YYYY-MM-DD HH24:MI') AS end_time,
instance_number
FROM dba_hist_snapshot
ORDER BY snap_id DESC
FETCH FIRST 20 ROWS ONLY;Sample output:
SNAP_ID BEGIN_TIME END_TIME INSTANCE_NUMBER
------- ---------------- ---------------- ---------------
4820 2026-06-20 14:00 2026-06-20 15:00 1
4819 2026-06-20 13:00 2026-06-20 14:00 1
4818 2026-06-20 12:00 2026-06-20 13:00 1
4817 2026-06-20 11:00 2026-06-20 12:00 1Note the snap_id of the snapshot taken just before your problem started (the begin snap) and the one taken just after it ended (the end snap). In this example, if the slowdown was between 13:00 and 14:00, you would use 4818 as begin and 4819 as end.
To check how many days of snapshots you have retained:
SELECT retention FROM dba_hist_wr_control;The default is 8 days (expressed as +00008 00:00:00). If your incident happened more than 8 days ago, the snapshots may already be purged.
Step 2: Run the AWR Report Script
Oracle ships ready-made scripts in $ORACLE_HOME/rdbms/admin. The scripts you will use most often:
| Script | Purpose |
|---|---|
awrrpt.sql |
AWR report for the current instance (single-instance or one RAC node) |
awrrpti.sql |
AWR report for a specific RAC instance you select |
awrddrpt.sql |
AWR diff report — compares two time periods |
ashrpt.sql |
ASH report — Active Session History for shorter windows |
Launch SQL*Plus as a privileged user and run the script:
sqlplus / as sysdba
SQL> @?/rdbms/admin/awrrpt.sqlThe @? notation expands to $ORACLE_HOME. You can also write the full path:
SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/awrrpt.sqlThe script prompts you interactively for five values:
1. Report type:
Enter value for report_type: htmlhtml. See the next section for why.
2. Number of days to display:
Enter value for num_days: 21 or 2.
3. Begin snapshot ID:
Enter value for bid: 48184. End snapshot ID:
Enter value for eid: 48195. Report filename:
Enter value for report_name: awr_20260620_1300_1400.htmlStep 3: HTML or Text Format?
Always choose HTML unless you have a specific reason not to.
| Format | Pros | Cons |
|---|---|---|
| HTML | Hyperlinked sections, sortable SQL tables, easy to navigate, best for DBA Copilot upload | Requires a browser to read |
| Text | Works in terminal-only environments, easy to grep |
Hard to read, no navigation, very long |
The HTML report opens in any web browser and has a table of contents at the top that links directly to each section. The Top SQL tables are sortable by elapsed time, CPU, or I/O with a single click.
Generating Without Interactive Prompts
For scripting and automation pipelines, you can call the underlying PL/SQL function directly and spool the output to a file:
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIMSPOOL ON
SET LONG 1000000
SPOOL /tmp/awr_report.html
SELECT output
FROM TABLE(
DBMS_WORKLOAD_REPOSITORY.awr_report_html(
l_dbid => (SELECT dbid FROM v$database),
l_inst_num => (SELECT instance_number FROM v$instance),
l_bid => 4818,
l_eid => 4819,
l_options => 0));
SPOOL OFFThis is useful for scheduled jobs that automatically collect AWR reports at the end of each batch window, or for scripts that collect evidence when a monitoring alert fires.
Generating AWR Reports in a RAC Environment
In Oracle RAC (Real Application Clusters), each node has its own instance number. You have two options:
Option 1: Report for a specific instance
Use awrrpti.sql, which asks you to select the instance number first:
SQL> @?/rdbms/admin/awrrpti.sqlThe script lists all instances in the cluster and asks which one to report on.
Option 2: Global RAC report
For a cluster-wide view, use awrgrpt.sql:
SQL> @?/rdbms/admin/awrgrpt.sqlThis is available from Oracle 11g R2 onwards and aggregates statistics across all instances.
To see which instances have snapshots:
SELECT DISTINCT instance_number, instance_name
FROM dba_hist_snapshot
ORDER BY instance_number;Common Errors and How to Fix Them
ORA-13516: AWR Operation failed Usually a permissions issue. Make sure you are connected as SYSDBA or a user with the Diagnostics Pack privilege and SELECT_CATALOG_ROLE.
ORA-20200: The instance was shutdown between snapshots X and Y
The database was restarted between your chosen snapshots. Select snapshots from within a single continuous uptime window. Check v$instance for the current startup time.
ORA-01555: Snapshot too old Rare during report generation but can happen on busy systems. Try regenerating the report immediately.
Report file is empty or very small (< 10 KB)
This usually means no AWR data exists for that snapshot range. Check that dba_hist_snapshot has rows for the snap_ids you entered, and that the SYSAUX tablespace is not full.
Script hangs at "Enter value for report_type"
You may be connected with a user that does not have SELECT on the AWR views. Reconnect as SYSDBA or grant the user SELECT_CATALOG_ROLE.
AWR data is missing for specific hours Check if the AWR snapshot job is running:
SELECT job_name, enabled, state
FROM dba_scheduler_jobs
WHERE job_name = 'GATHER_STATS_JOB'
OR job_class = 'AUTO_TASKS_INLINE_CLASS';Also check the snapshot interval:
SELECT snap_interval, retention FROM dba_hist_wr_control;If snapshots are taken every 60 minutes, a 15-minute incident may fall entirely within a single snapshot with no useful comparison.
Adjusting Snapshot Frequency for Busy Systems
The default 60-minute interval is too coarse for catching short incidents. You can reduce it to 15 or 30 minutes:
-- Change snapshot interval to 30 minutes, keep for 14 days
EXECUTE DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
interval => 30,
retention => 14 * 24 * 60);Note that more frequent snapshots consume more SYSAUX space. Check available space before reducing the interval on production systems.
You can also take a manual snapshot immediately before and after a planned operation:
-- Take a manual snapshot
EXECUTE DBMS_WORKLOAD_REPOSITORY.create_snapshot();
-- Check the snap_id of the snapshot just created
SELECT MAX(snap_id) FROM dba_hist_snapshot;What to Read First in the AWR Report
Once you have the HTML report open, resist the urge to scroll from top to bottom. The report has 50+ sections — most of them are not relevant to your specific problem. Go directly to:
1. Top 10 Foreground Events by Total Wait Time This is the most important section. It tells you where time was actually spent. Common patterns:
| Dominant wait event | Likely root cause |
|---|---|
db file sequential read |
Missing index, full table scan, buffer cache too small |
db file scattered read |
Full table scan or full index scan |
log file sync |
Excessive commits, slow I/O on redo log |
latch: shared pool |
Hard parsing, cursor sharing issues |
enq: TX - row lock contention |
Application-level row locking |
CPU |
CPU-intensive SQL, inefficient execution plans |
2. Load Profile Shows DB time per second, logical reads, physical reads, executes, and parses. This frames the scale of the workload and whether it differs from your baseline.
3. SQL Ordered by Elapsed Time The specific SQL statements consuming the most total time. Start here for tuning candidates.
4. SQL Ordered by Gets Statements consuming the most logical I/O (buffer gets). High gets with low elapsed time means the query runs fast individually but is called millions of times.
5. Instance Activity Statistics
Look at redo size, user commits, user rollbacks, and parse count (hard) for application-level patterns.
For a deeper dive into wait events and what they mean, see our companion guide: Oracle Wait Events Explained.
Statspack: The Free Alternative
If your database is not licensed for the Diagnostics Pack, use Statspack. It provides similar information to AWR and has been included with Oracle since version 8i at no additional cost.
Installing Statspack (first time only):
-- Connect as SYSDBA
@$ORACLE_HOME/rdbms/admin/spcreate.sql
-- You will be prompted to set a password for the PERFSTAT user
-- and choose a tablespace for the dataTaking a Statspack snapshot:
CONNECT perfstat/your_password
EXECUTE statspack.snap;Generating a Statspack report:
CONNECT perfstat/your_password
@$ORACLE_HOME/rdbms/admin/spreport.sql
-- Enter begin and end snap IDs when prompted
-- The report is saved as sp_*.lst in your current directoryStatspack reports are text-only (no HTML), but they follow the same structure as AWR reports: Top 5 Timed Events, Load Profile, Top SQL. Most of the reading techniques described above apply equally to Statspack.
Frequently Asked Questions
How long does it take to generate an AWR report?
Usually 30 seconds to 2 minutes for a standard report. Very large databases with many SQL statements in the repository can take up to 5 minutes. If it hangs for more than 10 minutes, check for blocking sessions in v$session.
Can I generate an AWR report without SYSDBA access?
Yes. Grant the user SELECT_CATALOG_ROLE and access to the AWR scripts. Some DBAs create a dedicated monitoring user with only the necessary privileges.
How much space does AWR use? By default, AWR uses 10% of the SYSAUX tablespace or 200 MB, whichever is smaller, growing as needed. On a busy database with many SQL statements, it can grow to several GB. Monitor with:
SELECT occupant_name, space_usage_kbytes / 1024 AS space_mb
FROM v$sysaux_occupants
WHERE occupant_name = 'SM/AWR';Can I copy AWR data from production to a non-production database?
Yes, using the DBMS_SWRF_HELPER package or AWR export/import. This lets you analyze production AWR data in a development environment without granting production access.
What is the difference between AWR and ASH? AWR captures cumulative statistics at snapshot intervals (default 60 min). ASH (Active Session History) samples active sessions every second and is ideal for diagnosing short incidents — under 30 minutes — with much more granularity. Use AWR for trend analysis and ASH for pinpointing specific events.
Analyze Your AWR Report in Seconds
Reading an AWR report well takes experience: knowing which section matters for which symptom, and what a "normal" value looks like for your workload.
DBA Copilot automates this. Upload your HTML AWR report and the AI extracts the key sections, correlates the wait profile with the Top SQL, identifies the root cause, and writes a plain-language diagnosis with prioritised recommendations — in seconds, without requiring a direct connection to your database.