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, system stats, segment activity — and stores them for 8 days. An AWR report is a diff between two snapshots: it tells you what the database actually did during that window.

If you're diagnosing a slow database, the AWR report is almost always the first artifact you should collect.

Licensing note: AWR is part of the Oracle Diagnostics Pack, which requires Enterprise Edition plus a separate license. If you're not licensed for it, use Statspack instead — it's free and ships with every edition.

When should you generate an AWR report?

• During or right after a performance incident — pick snapshots that bracket the slow period as tightly as possible.
• For a representative peak window — e.g. month-end batch, the busiest hour of the day.
• Never across an instance restart — cumulative counters reset on startup, so a report spanning a bounce produces garbage.

The single most common mistake is choosing a window that's too wide. A 24-hour report averages your 3-minute spike into noise. Aim for the shortest window that still contains the problem — typically 30 to 60 minutes.

Step 1: Find the snapshot IDs

Connect with a user that has access to the AWR views and list 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
FROM   dba_hist_snapshot
ORDER  BY snap_id DESC
FETCH  FIRST 20 ROWS ONLY;

Note the snap_id just before your problem started (the begin snap) and the one just after it ended (the end snap).

Step 2: Run the report script

Oracle ships ready-made scripts in $ORACLE_HOME/rdbms/admin. The two you'll use most:

Launch SQL*Plus as a privileged user and run:

sqlplus / as sysdba
SQL> @?/rdbms/admin/awrrpt.sql

The script will prompt you for four things:

1. Report type — html or text (see below).
2. Number of days of snapshots to list — enter 1 or 2.
3. Begin snapshot ID — the snap_id from Step 1.
4. End snapshot ID — the later snap_id.
5. Report name — accept the default or give it a meaningful name.

The file is written to your current working directory.

Step 3: HTML or text?

Choose HTML. It's far easier to read, the sections are hyperlinked, and the Top SQL tables are sortable in a browser. Reserve text format for when you need to grep the output or paste it into a terminal-only environment.

Enter value for report_type: html

Generating without the interactive prompts

For scripting and automation, call the underlying function directly. This emits the HTML for a given snapshot range as rows you can spool to a file:

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     => 4815,   -- begin snap_id
           l_eid     => 4816)); -- end snap_id

What to read first

Once you have the report open, resist the urge to scroll top to bottom. Go straight to:

1. Top 10 Foreground Events by Total Wait Time — where the time actually went. If db file sequential read dominates, you have an I/O / indexing story; if it's log file sync, a commit story; and so on.
2. Load Profile — DB time per second, logical/physical reads, executes, parses. This frames the scale of the workload.
3. SQL ordered by Elapsed Time / Gets — the specific statements to tune.

If you want to go deeper on the wait events themselves, see our companion guide, Oracle Wait Events Explained.

A faster path

Reading an AWR report well takes experience — knowing which section matters for which symptom, and what a "normal" number looks like for your workload. That's exactly what DBA Copilot automates: upload the HTML report and it extracts the key sections, correlates the wait profile with the Top SQL, and writes a plain-language diagnosis with prioritized recommendations.