"The database was slow between 10 and 11. Why?" Every Oracle DBA hears it. The answer is almost always already recorded inside the database, in the Automatic Workload Repository. The challenge is knowing which of Oracle's many report scripts to run, what to type at each prompt, and where to look in a 60-page report.
After years of performance benchmarking on Oracle RAC, these reports are my daily tools. This guide covers all 17 scripts in $ORACLE_HOME/rdbms/admin, explains every prompt, shows how to generate reports without prompts for automation, how to set up AWR properly, and, most importantly, how to read the result.
1. AWR vs ASH vs ADDM
- Snapshots of cumulative statistics, every 60 min by default
- Report = the difference between two snapshots
- Best for: "what did the database do between 10:00 and 11:00?"
- Samples every active session once per second
- In memory, and 1 in 10 samples kept on disk in AWR
- Best for: short spikes, "what happened at 10:07?", a single session or SQL
- Analyses an AWR snapshot pair for you
- Runs automatically after every snapshot
- Best for: ranked findings and recommendations in plain text
2. Which report do I need?
| Your question | Run this |
|---|---|
| How did the database behave during a period (an hour, a batch window)? | AWR awrrpt.sql |
| What happened during a short spike, or for one session, SQL, module or PDB? | ASH ashrpt.sql / ashrpti.sql |
| Just tell me what is wrong and what to do | ADDM addmrpt.sql |
| Why is today slower than last week? Did the upgrade change anything? | COMPARE awrddrpt.sql |
| How did the whole RAC cluster behave? | RAC awrgrpt.sql |
| What did one SQL_ID do, and did its execution plan change? | SQL awrsqrpt.sql |
| I need to analyse AWR data on another server, or keep it after retention | MOVE awrextr.sql + awrload.sql |
3. All 17 scripts at a glance
All scripts live in $ORACLE_HOME/rdbms/admin. In SQL*Plus, ? is a shortcut for ORACLE_HOME, so @?/rdbms/admin/awrrpt.sql works from any directory. The pattern is simple: the plain name reports on the current database and instance, and the …i version lets you pick any DBID and instance (another RAC node, or AWR data loaded from another system).
| Script | What it produces | Any DBID / instance | |
|---|---|---|---|
| AWR | awrrpt.sql | AWR report, current instance | awrrpti.sql |
| RAC | awrgrpt.sql | Global AWR report, all RAC instances | awrgrpti.sql |
| COMPARE | awrddrpt.sql | Compare two periods, one instance | awrddrpi.sql |
| RAC | awrgdrpt.sql | Compare two periods, RAC global | awrgdrpi.sql |
| SQL | awrsqrpt.sql | History and plans of one SQL_ID | awrsqrpi.sql |
| ASH | ashrpt.sql | ASH report, current instance | ashrpti.sql |
| ADDM | addmrpt.sql | ADDM findings for a snapshot pair | addmrpti.sql |
| MOVE | awrextr.sql | Export AWR snapshots to a dump file | — |
| MOVE | awrload.sql | Import an AWR dump file | — |
| MOVE | awrinfo.sql | AWR/SYSAUX space usage and health | — |
4. Before you run anything
CONTROL_MANAGEMENT_PACK_ACCESS must be DIAGNOSTIC or DIAGNOSTIC+TUNING. Without the licence, use Statspack, which is free.Find the snapshot IDs first. Every AWR-based report asks for a begin and end snapshot:
SELECT snap_id, instance_number,
TO_CHAR(begin_interval_time,'DD-MON HH24:MI') AS begin_time,
TO_CHAR(end_interval_time,'DD-MON HH24:MI') AS end_time
FROM dba_hist_snapshot
WHERE begin_interval_time > SYSDATE - 1
ORDER BY snap_id, instance_number;
- Pick snapshots that tightly wrap the problem. A 1-hour report hides a 5-minute spike; for short spikes use ASH.
- A report cannot span an instance restart. Statistics reset at startup, so the script refuses snapshot pairs across one.
- Run reports as a user with the DBA role (or SELECT_CATALOG_ROLE plus EXECUTE on DBMS_WORKLOAD_REPOSITORY).
- Reports are written to the directory where you started SQL*Plus, unless you give a full path as the report name.
- Multitenant (12.2+): run from CDB$ROOT for the whole CDB. Inside a PDB, the scripts also ask whether to use AWR_ROOT data (the CDB's snapshots) or AWR_PDB data (the PDB's own snapshots).
5. AWR reports
awrrpt.sql: the classic AWR report
SQL> @?/rdbms/admin/awrrpt.sql
| Prompt | What to enter |
|---|---|
report_type | html (default, easiest to read), text (good for diff tools and email), or active-html (interactive Performance Hub view) |
num_days | How many days of snapshots to list. Press Enter to list all |
begin_snap / end_snap | The snapshot pair around the problem period |
report_name | Press Enter for the default awrrpt_<inst>_<begin>_<end>.html, or give a full path such as /tmp/awr_prod_0900_1000.html |
awrrpti.sql: any database, any instance
Same report, plus prompts for dbid and inst_num. Use it for another RAC node, or for AWR data loaded from another database with awrload.sql.
6. ASH reports
ashrpt.sql: what was active, minute by minute
SQL> @?/rdbms/admin/ashrpt.sql
| Prompt | What to enter |
|---|---|
report_type | html (default) or text |
begin_time | Absolute, e.g. 10:05 or 10/27/26 10:05:00; or relative, e.g. -30 (30 minutes ago) or -1:15. Default: 15 minutes ago |
duration | Minutes from begin_time. Default: up to now |
report_name | Default ashrpt_<inst>_<MMDD>_<HHMI>.html |
ashrpti.sql: ASH with filters
The most powerful report in the set. On top of dbid, instance (enter ALL for every RAC instance), time window and slot_width (the bucket size in the Activity Over Time section), you can narrow the report to one or more targets. Leave any of them empty to ignore it:
| Target prompt | Example | Use it to answer |
|---|---|---|
target_session_id | 1234 | What was this one session doing? |
target_sql_id | 8w9n5yh2ppq1f | Where did this SQL spend its time? |
target_wait_class | User I/O (or CPU) | Who was waiting on I/O (or burning CPU)? |
target_service_hash | from V$ACTIVE_SERVICES | Activity of one application service |
target_module_name / target_action_name | BATCH% | Activity of one program or job step |
target_client_id | value of CLIENT_IDENTIFIER | One end user in a connection-pooled app |
target_plsql_entry | APP.PKG_BILLING.% | Time spent under one PL/SQL package |
target_container | SALESPDB | Activity of one PDB |
V$ACTIVE_SESSION_HISTORY (1-second samples). Older periods come from DBA_HIST_ACTIVE_SESS_HISTORY, which keeps 1 sample in 10. Expect less detail for older time ranges.7. ADDM reports
SQL> @?/rdbms/admin/addmrpt.sql -- current instance SQL> @?/rdbms/admin/addmrpti.sql -- choose dbid and instance
| Prompt | What to enter |
|---|---|
num_days | Days of snapshots to list |
begin_snap / end_snap | Snapshot pair from the same instance, with no restart in between |
report_name | Default addmrpt_<inst>_<begin>_<end>.txt. ADDM reports are text only |
Each finding shows its impact (percentage of DB time), the recommendation (for example, tune a SQL_ID, resize a memory area, or check I/O), and the rationale behind it. Read the top two or three findings first; they usually account for most of the lost time.
8. Compare two periods
SQL> @?/rdbms/admin/awrddrpt.sql -- current instance SQL> @?/rdbms/admin/awrddrpi.sql -- choose dbid/instance for each period
| Prompt | What to enter |
|---|---|
report_type | html or text |
num_days / begin_snap / end_snap | The first period, e.g. last Monday 10:00–11:00 (the good run) |
num_days2 / begin_snap2 / end_snap2 | The second period, e.g. today 10:00–11:00 (the slow run) |
report_name | Default awrdiff_… |
9. RAC global reports
SQL> @?/rdbms/admin/awrgrpt.sql -- all instances, one cluster-wide report SQL> @?/rdbms/admin/awrgrpti.sql -- choose dbid and a list of instances (e.g. 1,3) or ALL SQL> @?/rdbms/admin/awrgdrpt.sql -- compare two periods, cluster-wide SQL> @?/rdbms/admin/awrgdrpi.sql -- compare two periods, chosen dbid/instances
On RAC, start with the global report: it shows every instance side by side, plus cluster sections such as Global Cache load and efficiency and interconnect statistics. Then drill into one node with awrrpti.sql if one instance stands out.
10. SQL-level report
SQL> @?/rdbms/admin/awrsqrpt.sql -- current instance SQL> @?/rdbms/admin/awrsqrpi.sql -- choose dbid and instance
| Prompt | What to enter |
|---|---|
report_type | html or text |
num_days / begin_snap / end_snap | The period to analyse |
sql_id | The SQL_ID, from the AWR report's SQL ordered by… sections, V$SQL or ASH |
It shows elapsed time, CPU, buffer gets and executions per plan hash value, plus the full text of every execution plan captured in AWR. When a query suddenly slows down, this is the fastest way to prove a plan change: two plan hash values with very different gets per execution.
11. Move AWR data to another system
Use this to analyse production performance on a test server, to send AWR data to a vendor, or to keep a snapshot range beyond the retention period. Both scripts run as SYS and use a directory object.
-- Source database
SQL> @?/rdbms/admin/awrextr.sql
-- prompts: dbid, num_days, begin_snap, end_snap,
-- directory_name (e.g. DATA_PUMP_DIR), file_name (without .dmp)
-- copy the .dmp file to the target server's directory, then on the target:
SQL> @?/rdbms/admin/awrload.sql
-- prompts: directory_name, file_name (some versions also ask for the staging
-- schema name, default AWR_STAGE, and its tablespaces); the staging
-- schema is dropped at the end
The loaded snapshots keep the source DBID. On the target, report on them with the …i scripts (awrrpti.sql, ashrpti.sql, addmrpti.sql) and choose that DBID.
awrinfo.sql: is AWR itself healthy?
@?/rdbms/admin/awrinfo.sql writes a text report (default awrinfo.txt) about the repository itself: SYSAUX usage by component, snapshot counts, retention and the largest AWR segments. Use it when SYSAUX grows unexpectedly, not to diagnose database performance.
12. Generate reports without prompts
The interactive scripts are fine for one report. For automation (a nightly report, a report per hour of a batch window, or a performance-test pipeline), call DBMS_WORKLOAD_REPOSITORY directly. Each report function returns the report as rows you can spool to a file.
-- awr_html.sql : usage @awr_html.sql <begin_snap> <end_snap> SET PAGESIZE 0 LINESIZE 8000 TRIMSPOOL ON FEEDBACK OFF HEADING OFF VERIFY OFF TERMOUT OFF COLUMN dbid NEW_VALUE v_dbid COLUMN inst NEW_VALUE v_inst SELECT dbid FROM v$database; SELECT instance_number AS inst FROM v$instance; SPOOL awr_&v_inst._&1._&2..html SELECT output FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(&v_dbid, &v_inst, &1, &2)); SPOOL OFF SET TERMOUT ON
Swap in the other functions for the other report types:
| Report | Function call |
|---|---|
| AWR, one instance | AWR_REPORT_HTML(dbid, inst, begin_snap, end_snap) |
| AWR, RAC global | AWR_GLOBAL_REPORT_HTML(dbid, '1,2', begin_snap, end_snap) |
| Compare two periods | AWR_DIFF_REPORT_HTML(dbid, inst, b1, e1, dbid, inst, b2, e2) |
| One SQL_ID | AWR_SQL_REPORT_HTML(dbid, inst, begin_snap, end_snap, 'sql_id') |
| ASH, last 30 minutes | ASH_REPORT_HTML(dbid, inst, SYSDATE-30/1440, SYSDATE) |
| Text instead of HTML | Use the …_TEXT variant of each function |
13. Manage AWR: snapshots, retention and baselines
The defaults (a snapshot every 60 minutes, kept for 8 days) are too short for most production systems. You cannot compare this month-end with last month-end if last month's snapshots are gone.
-- Current settings
SELECT snap_interval, retention, topnsql FROM dba_hist_wr_control;
-- Snapshot every 30 minutes, keep 35 days (both values in MINUTES)
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 30, retention => 35*24*60);
-- Take a snapshot right now (before and after a test run or a batch job)
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
-- Keep a known-good period forever as a reference baseline
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(start_snap_id => 4510, end_snap_id => 4512, -
baseline_name => 'MONTH_END_GOOD_OCT26');
SELECT baseline_name, start_snap_id, end_snap_id FROM dba_hist_baseline;
awrinfo.sql after a few days. From 19c, AWR data can also be placed in a tablespace other than SYSAUX (tablespace_name parameter).14. Read an AWR report in 5 minutes
An AWR report has dozens of sections. In this order, these seven answer most questions:
| Top event | Usually points to |
|---|---|
| DB CPU | Normal when on top; check that the SQL using it is efficient (logical reads per execution) |
| db file sequential read | Index or single-block reads: inefficient plans, or slow storage (check latency) |
| db file scattered read / direct path read | Full scans: missing index, or an intended scan that is just big |
| log file sync | Commit latency: too-frequent commits, or slow redo writes (compare with log file parallel write) |
| enq: TX - row lock contention | Sessions blocking each other on the same rows: application design |
| gc buffer busy acquire/release, gc cr/current block busy (RAC) | Hot blocks shared across instances; check the global report and interconnect |
| library cache / cursor: pin S wait on X | Hard parsing, invalidations, or high-concurrency parsing |
15. Pro tips and common mistakes
Cheat sheet
@?/rdbms/admin/awrrpt.sql AWR, this instance @?/rdbms/admin/awrrpti.sql AWR, any dbid/instance @?/rdbms/admin/awrgrpt.sql AWR, RAC global @?/rdbms/admin/awrddrpt.sql compare two periods @?/rdbms/admin/awrgdrpt.sql compare, RAC global @?/rdbms/admin/awrsqrpt.sql one SQL_ID and its plans @?/rdbms/admin/ashrpt.sql ASH, this instance @?/rdbms/admin/ashrpti.sql ASH with filters / ALL @?/rdbms/admin/addmrpt.sql ADDM findings @?/rdbms/admin/awrinfo.sql AWR space and health @?/rdbms/admin/awrextr.sql export AWR snapshots @?/rdbms/admin/awrload.sql import AWR snapshots
Conclusion
Pick the report that matches the question: AWR for a period, ASH for a moment, a session or a SQL, ADDM for a second opinion, and the diff report when something changed. Give AWR enough retention, keep baselines of good periods, and read the report top-down by time, not by ratios. With that routine, "why was it slow?" becomes a question you can answer in minutes.
Which AWR section do you read first? Tell me in the comments.
References
- Oracle 19c PL/SQL Packages: DBMS_WORKLOAD_REPOSITORY
- Transporting Automatic Workload Repository Data to Another System
- Managing AWR in a Multitenant Environment
- AWR_PDB_AUTOFLUSH_ENABLED
Tamilselvan Perumal · Oracle ACE Apprentice · Lead Product Engineer & Senior DBA, Performance & Benchmarking
.png)
){kind=link}