How to Read an Oracle AWR Report: The 10-Step Method to Find the Bottleneck (19c to 26ai)

Published Sep 2026 · 14 min read

An AWR report for one busy hour runs to dozens of pages and hundreds of tables. Most DBAs open it, scroll, stare at Buffer Hit % and close it again. The DBAs who find the problem in fifteen minutes do something different: they read about ten sections, always in the same order, and ignore the rest until one of those ten sends them there.

This is that order. Each step says what to look at, what "bad" looks like, and which step to jump to next. Section names and columns are from Oracle Database 19c and Oracle AI Database 26ai reports; the layout has been stable since 12c, so the method works on 12.2 and 21c as well.

Before you start: generate the right report

A good diagnosis starts with a good window. The single most common mistake is a 24-hour report: a 20-minute spike averaged over a day disappears. Pick the snapshots that bracket the problem as tightly as possible, usually one hour, and generate a second report for the same hour on a normal day so you have something to compare against.

-- which snapshots cover the problem window?
SELECT snap_id, instance_number,
       TO_CHAR(begin_interval_time,'DD-MON HH24:MI') begin_time,
       TO_CHAR(end_interval_time,'HH24:MI')          end_time
FROM   dba_hist_snapshot
WHERE  begin_interval_time > SYSDATE - 1
ORDER  BY snap_id, instance_number;

-- the interactive scripts (run in SQL*Plus as a DBA user)
@?/rdbms/admin/awrrpt.sql     -- this instance, choose html
@?/rdbms/admin/awrgrpt.sql    -- RAC: all instances in one report
@?/rdbms/admin/awrddrpt.sql   -- compare two periods side by side
@?/rdbms/admin/awrsqrpt.sql   -- one SQL_ID and all its plans
@?/rdbms/admin/ashrpt.sql     -- Active Session History for a short spike

If you want the report without answering prompts, for a script or a scheduled job, call the package directly and spool the HTML:

SET PAGES 0 LINES 32767 TRIMSPOOL ON HEADING OFF FEEDBACK OFF VERIFY OFF
COLUMN dbid NEW_VALUE dbid
SELECT dbid FROM v$database;

SPOOL awr_1200_1201.html
SELECT output
FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(&dbid, 1, 1200, 1201));
SPOOL OFF

Two settings decide whether the report you need will exist at all. The defaults are a snapshot every 60 minutes kept for 8 days. For a production system, 30-minute snapshots kept for 30 days make incident reviews far easier, and a saved baseline stops a known-good week from being purged.

SELECT snap_interval, retention FROM dba_hist_wr_control;

-- 30-minute snapshots, 30 days retention (both in minutes)
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 43200, interval => 30);

-- bracket a test or a batch run with manual snapshots
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

-- keep a normal period forever as a comparison point
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(start_snap_id => 1180, end_snap_id => 1204, baseline_name => 'normal_week');

A licensing note that matters: AWR, ASH and ADDM are part of the Diagnostics Pack, which is licensed with Enterprise Edition. On Standard Edition, or without the pack, use Statspack; most of the reading method below applies to a Statspack report too.

Step 1: the header — DB Time, Elapsed and Average Active Sessions

The first four lines tell you how busy the database was, and every other number in the report should be read against them. Find Elapsed and DB Time, both in minutes. DB Time is the total time foreground sessions spent working or waiting inside the database, so dividing it by Elapsed gives the Average Active Sessions (AAS): how many sessions were in the database at any moment, on average.

Compare AAS with the CPU count shown in the Host CPU section. An AAS of 8 on a 32-core server is a lightly loaded database that happens to have a slow query. An AAS of 8 on a 4-core server means sessions are queueing for CPU or waiting on something, and the rest of the report will tell you which. If DB Time is tiny compared with Elapsed, the database was mostly idle, and the slowness the users felt is probably in the application tier or the network — a useful thing to know before you spend an hour in the SQL section.

Step 2: Load Profile — what changed compared with a normal day

The Load Profile gives per-second and per-transaction rates. On its own it describes the workload; next to the same hour on a good day it tells you what changed. Read the per-second column and look for the lines that moved most.

• Redo size jumping means a job is changing far more data than usual — often a batch, a mass update or a missing NOLOGGING on a reload.
• Logical read and Physical read rising while Executes stays flat means the same statements are doing more work per execution: a plan change is the first suspect.
• Hard parses in the tens or hundreds per second on an OLTP system usually means literal SQL instead of bind variables.
• Logons of more than a handful per second points at an application opening a new connection per request instead of using a pool.
• Transactions and User calls up with everything else in proportion means the database simply received more work — a capacity conversation, not a tuning one.

Skip the Instance Efficiency Percentages. A Buffer Hit % of 99.9 can hide a query doing ten million logical reads per execution, and a lower one can be perfectly healthy for a reporting system. They are ratios without context; the next step is where the evidence is.

Step 3: Top 10 Foreground Events — the most important table in the report

This table ranks where foreground sessions spent their time. Read two columns before anything else: % DB time, which tells you whether the event matters, and Avg Wait, which tells you whether each wait was slow or whether there were simply a lot of them. An event with 3% of DB time is not your problem no matter how alarming its name sounds.

DB CPU at the top is common and often healthy — it means sessions were working rather than waiting. If it is at the top and the system is slow, the question becomes which SQL is burning the CPU, and you go straight to Step 6 and the SQL ordered by CPU Time and by Gets.

Event at the top	What it usually means	Where to go next
db file sequential read	Single-block reads, mostly index access. Many fast waits means SQL reading too much; slow waits mean storage.	SQL ordered by Reads, Segments by Physical Reads, Step 8
db file scattered read / direct path read	Full scans. Direct path read is a large full scan that bypasses the buffer cache.	SQL ordered by Reads, missing index or changed plan
log file sync	Sessions waiting for commit. Compare with log file parallel write in Background Wait Events.	Commit frequency, redo storage, CPU
enq: TX - row lock contention	Application-level locking: one session holds rows others want.	Segments by Row Lock Waits, the application team
library cache: mutex X / cursor: pin S wait on X	Parsing contention, very hot statements or high version counts.	Time Model, SQL ordered by Parse Calls and Version Count
direct path read temp / direct path write temp	Sorts and hash joins spilling to TEMP.	SQL ordered by Elapsed, PGA advisory in Step 9
gc buffer busy acquire / gc cr block busy	RAC nodes fighting over the same blocks.	Segments by Global Cache Buffer Busy, service placement
resmgr:cpu quantum	Resource Manager is deliberately throttling sessions.	Your Resource Manager plan, Instance CPU in Step 5

Two rules of thumb for latency, to use with judgement rather than as law. On modern SSD or flash storage, db file sequential read normally averages around a millisecond or less; sustained averages above 5 ms deserve a conversation with the storage team, and above 10 ms almost always do. For log file sync, if its average is much higher than log file parallel write, the redo disk is fine and the delay is elsewhere — usually CPU starvation or an application committing after every row. If both are high, the redo storage itself is slow.

Step 4: Time Model Statistics — where DB Time actually went

The Time Model breaks DB Time into activities, each with a % of DB Time. The lines overlap, so they do not add up to 100%, but a few of them answer questions the wait events cannot.

• sql execute elapsed time near 90% or more is normal: time is going into running SQL, so the SQL section is where the answer lives.
• parse time elapsed and hard parse elapsed time taking a double-digit share is a parsing problem, confirming what the Load Profile suggested about literals.
• PL/SQL execution elapsed time being large sends you to the PL/SQL code rather than the SQL it calls.
• connection management call elapsed time being visible at all means logon and logoff are costing real time — back to the connection pool.

Step 5: Host CPU and Instance CPU — is the server itself the bottleneck?

Host CPU shows the whole server: CPUs, cores, load average at the start and end, and %Idle. Instance CPU shows how much of that this database used. Read them together.

• Host %Idle near zero and a load average well above the CPU count: the server is saturated. Anything that waits in this report may simply be waiting for a CPU.
• Host busy but %Total CPU for the instance low: something else on the box — another database, a backup agent, an antivirus scan — is eating the CPU.
• %DB time waiting for CPU (Resource Manager) above zero: sessions were throttled by design, which matches resmgr:cpu quantum in Step 3.

Step 6: SQL Statistics — find the statements responsible

This is where most investigations end. Start with SQL ordered by Elapsed Time, then open the list that matches what Step 3 told you: by CPU Time for DB CPU, by User I/O Wait Time and by Reads for read waits, by Gets for logical-read-heavy work, by Parse Calls and by Version Count for parsing problems, and by Cluster Wait Time on RAC.

For each top SQL_ID, read Executions, Elapsed Time per Exec and %Total together. They separate the two patterns that need completely different fixes:

• Few executions, huge time per execution — a report or batch statement running badly. This is almost always a plan problem: a missing index, stale statistics, or a plan that changed. Go to Step 10 and check its plan history.
• Millions of executions, tiny time per execution — each call is fine, the application is calling it far too often. Tuning the SQL will barely help; caching, batching or fixing a loop in the application will.

The %CPU and %IO columns tell you what each statement spent its time on, which should agree with the top wait events. If the top event is db file sequential read and the top SQL shows 85% IO, you have found the connection.

Step 7: Segment Statistics — which table or index is hot

Wait events tell you what kind of trouble you have; Segment Statistics tell you which object it is happening on. Open the list that matches your top event: Segments by Logical Reads or Physical Reads for read-heavy problems, Segments by Row Lock Waits for TX contention, Segments by ITL Waits when too many transactions try to change the same block, and Segments by Buffer Busy Waits for hot blocks. On RAC, Segments by Global Cache Buffer Busy names the objects the nodes are fighting over.

One index taking half of all physical reads, on a table that the top SQL also touches, turns a vague "the database is slow" into a single specific fix.

Step 8: IO Stats — is the storage slow, or just busy?

IOStat by Function summary shows which component did the I/O — buffer cache reads, direct reads, LGWR, DBWR, RMAN — so you can see immediately if a backup was running in the middle of your problem hour. Tablespace IO Stats and File IO Stats then show Av Rd(ms) and Av 1-bk Rd(ms) for each tablespace and datafile.

Uniformly slow reads across every file point at the storage layer or the path to it. One tablespace or one file much slower than the rest points at a specific disk group, LUN or filesystem. Fast reads with huge read counts mean the storage is fine and the SQL is asking for too much — back to Step 6.

Step 9: Advisories — estimates, not instructions

The advisory section estimates what would happen with different memory sizes. PGA Memory Advisory is the most useful: if Estd PGA Overalloc Count is above zero at the current size, or Estd Extra W/A MB Read/Written to Disk drops sharply at a larger size, more PGA would reduce the TEMP spills you saw in Step 3. The SGA Target Advisory and Buffer Pool Advisory estimate DB time and physical reads at other cache sizes.

Treat them as supporting evidence. They are modelled from one interval, they cannot see a bad plan that will keep reading however big the cache is, and a memory change made from a single report often moves the problem rather than fixing it.

Step 10: confirm before you change anything

By now you have a suspect: a statement, an object, a resource. Before touching production, confirm it with the tools that look at the same data from another angle.

-- 1. What changed? Compare the bad hour with the same hour on a good day
@?/rdbms/admin/awrddrpt.sql

-- 2. A spike shorter than the snapshot interval: ASH shows it minute by minute
@?/rdbms/admin/ashrpt.sql

-- 3. Did this SQL change plan? Every plan AWR has captured for it
@?/rdbms/admin/awrsqrpt.sql
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));

-- 4. Oracle's own analysis of the same snapshot range
@?/rdbms/admin/addmrpt.sql

If DISPLAY_AWR shows a new plan hash value that first appeared the day the problem started, you have your root cause and a quick, reversible fix: load the old plan as a SQL plan baseline while you work out why the optimizer changed its mind.

Putting it together: how the steps connect

An illustrative walk-through, with round numbers, of how the steps lead from one to the next. The header shows 60 minutes elapsed and 540 minutes of DB Time: an AAS of 9 on an 8-core server, so sessions are queueing. The Load Profile, next to last Tuesday, shows physical reads per second up five times while executions are flat. The top foreground event is db file sequential read at 48% of DB time with a 0.6 ms average — fast storage, too many reads. SQL ordered by Reads shows one SQL_ID responsible for 41% of all physical reads; Segments by Physical Reads names an index on the ORDERS table; IO Stats shows every file reading quickly. DISPLAY_AWR for that SQL_ID shows a new plan the morning after the weekend statistics job. The fix is a plan baseline, not faster storage and not a bigger buffer cache — both of which the ratios and advisories alone might have suggested.

Common mistakes when reading AWR

• A 24-hour window. The spike averages away. Bracket the problem as tightly as the snapshots allow.
• Tuning by ratio. Buffer Hit % and Library Hit % describe averages, not problems. Start from DB Time and % DB time.
• Chasing an event with a scary name and 2% of DB time. Fix the thing consuming the most time first.
• Reading a single-instance report on RAC. Use awrgrpt.sql so you see every node, or the problem can be on the instance you did not look at.
• No comparison report. Without a normal hour to compare against, you cannot tell unusual from normal. awrddrpt.sql takes two minutes.
• Changing parameters from the advisory section. Advisories estimate; a bad plan will still be a bad plan with twice the memory.

FAQ

What snapshot interval and retention should I use for AWR?

The default is 60 minutes kept for 8 days. For production, 30-minute snapshots with 30 days of retention give a sharper picture of short incidents and let you compare with the same day last month, at the cost of a larger SYSAUX tablespace. Set both with DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS, and save important normal periods as baselines so they are never purged.

Is AWR free to use?

No. AWR, ASH and ADDM, including the DBA_HIST views they are built on, require the Diagnostics Pack, which is licensed with Enterprise Edition. Without it, Statspack gives you a similar report, and the reading order in this guide — header, load profile, top events, time model, CPU, SQL, segments, IO — works on a Statspack report too.

What is the difference between AWR, ASH and ADDM?

AWR stores snapshots of cumulative statistics, so a report shows totals and averages between two snapshots. ASH samples every active session once a second, so it can show a two-minute spike that an hourly AWR report averages away. ADDM reads the AWR data and writes its own findings and recommendations. Use AWR to find the area, ASH to zoom in on time, and ADDM as a second opinion.

How do I get an AWR report for a single PDB?

Run the report from CDB$ROOT for instance-wide problems — CPU, memory, redo and I/O belong to the instance, not to a PDB. Automatic PDB-level snapshots are off by default (awr_pdb_autoflush_enabled is FALSE); once enabled in the PDB, running awrrpt.sql inside it lets you choose the PDB's own AWR data instead of the root's.

What does DB Time mean in an AWR report?

DB Time is the total time foreground sessions spent in database calls, working on CPU or waiting on non-idle events, summed across all sessions. Because it is summed, it can be much larger than the elapsed time: 540 minutes of DB Time in a 60-minute window means an average of nine sessions active at once.

Next step

Reading AWR well is a skill you build on real reports, not on screenshots. Our Oracle 26ai DBA training includes a dedicated Oracle Performance Tuning module, six live hours covering AWR, ASH and ADDM on a working database, and it sits alongside the rest of our live Oracle DBA training programs. For the queries to run once AWR has pointed you at a problem, keep The 2 AM Oracle DBA Toolkit bookmarked, and for every statistic in the report, Oracle's Database Performance Tuning Guide is the official reference.