How to Read an Oracle AWR Report in 19c
A practical reading order from real production incidents, not a feature tour.
An AWR report is a snapshot of where your instance spent its time. Reading it in the right order is half the battle.
02:14. The on-call page hit: checkout API p95 had jumped from 180 ms to 4.2 seconds. No errors. No node eviction. No failover. Just a database that had quietly gone slow under a normal load. The first artifact I pulled was a one-hour AWR report, and within four minutes it pointed straight at the cause.
If you have ever stared at a 30-page AWR report and not known where to look first, this guide is for you. Knowing how to read an Oracle AWR report in 19c is not about understanding every section. It is about reading a handful of sections in the right order so you can go from "the database is slow" to "this SQL on this object is the problem" in minutes. That is exactly what I did at 02:14, and it is the workflow I will walk you through here.
AWR (Automatic Workload Repository) takes regular snapshots of performance statistics and stores them in the SYSAUX tablespace. A report compares two snapshots and shows you the delta: what the instance did, where it waited, and which statements drove the load. The trick is to stop reading top to bottom and start reading by importance.
Junior and mid-level DBAs who can generate an AWR report but freeze when it comes to interpreting it, and senior engineers who want a tighter triage checklist for incidents. Examples use Oracle 19c, but the reading order applies to 12c and 18c as well.
What this guide covers
- What an AWR report actually tells you
- How to read an Oracle AWR report in 19c: the 6-step order
- Steps 1 to 3: the 90-second triage
- Step 4: Top 10 Foreground Wait Events (the heart)
- Step 5: SQL Statistics, mapping waits to SQL
- Generating the report in 19c
- What NOT to do with an AWR report
- Frequently asked questions
The 6-step reading order and the key metrics to watch in an Oracle 19c AWR report.
What an AWR report actually tells you
An AWR report answers one question: during this window, where did the database spend its time? Everything else is supporting detail. The single most important concept is DB Time, the total time spent by sessions inside the database, either working on CPU or waiting on something. If you understand DB Time and where it went, you understand the report.
Think of it like a budget. DB Time is the total money spent. The Top Wait Events section is the itemised bill. The SQL Statistics section names who was spending it. Your job is to read the bill, not to audit every line item in the building.
My reference environment for this guide: Oracle 19c (19.21), three-node RAC, roughly 8 TB database, peak around 4,500 transactions per second, on 32-core nodes. On that system a healthy one-hour report shows DB Time well within the combined core count. When a single instance shows DB Time several multiples of its 32 cores, I know sessions were queuing before I read another line.
How to read an Oracle AWR report in 19c: the 6-step order
Here is the order I read every report in, regardless of the symptom. The goal of the first three steps is a fast sanity check. The real diagnosis happens in steps four and five.
The reading order
- Header and DB Time - confirm the window, instance, and how busy the DB was
- Load Profile - per-second and per-transaction rates, spot the spike
- Instance Efficiency - a quick sanity check, not a scorecard
- Top 10 Foreground Wait Events - where DB Time actually went
- SQL Statistics - the statements behind those waits
- Advisories and Segment Statistics - confirm the fix and find the object
Reading direction matters. Reports are laid out roughly in this order anyway, but the instinct most people fight is the urge to read carefully from the top. Skim steps one to three, then spend your real attention on the waits and the SQL.
Steps 1 to 3: the 90-second triage
Step 1: Header and DB Time
The header confirms the instance name, the host, the database version, and the snapshot interval. Check that you are looking at the right instance and the right window. Then find the DB Time line near the top. Compare DB Time against the Elapsed Time. DB Time divided by elapsed time gives you the average number of active sessions. Hold that number against your CPU core count.
Step 2: Load Profile
The Load Profile gives you rates per second and per transaction: logical reads, physical reads, redo size, executes, and parses. You are not memorising numbers here. You are looking for the one metric that is wildly out of line with a normal day. A 10x jump in hard parses per second, for example, screams "missing bind variables" before you read anything else.
Step 3: Instance Efficiency
This is the section most often misused. Buffer Hit and Library Hit percentages look reassuring near 99 percent, but a high ratio does not mean a healthy database. A runaway query doing millions of buffer gets can show a 99.9 percent buffer hit ratio while crippling the instance. Glance at it, then move on. Never tune toward a ratio.
At 02:14 the Load Profile told the story before I reached the waits. Logical reads per second had gone from a baseline near 1.2 million to over 9 million, while transactions per second were flat. Same work, far more gets. That pattern, more reads with no more business volume, almost always means a plan flip on a heavy statement. The buffer hit ratio, meanwhile, sat at a smug 99.8 percent.
Step 4: Top 10 Foreground Wait Events (the heart)
This is where you spend your attention. The Top 10 Foreground Wait Events section, in 19c, ranks the events foreground sessions waited on, by total wait time and as a percentage of DB Time. Whatever sits at the top of this list is, by definition, where your database spent most of its non-CPU time.
A few common patterns and what they usually point to:
- db file sequential read high - single-block I/O, often index reads. Frequently a sign of physical reads that should have been cached, or a plan using the wrong index.
- db file scattered read high - multi-block I/O, classic full table scans. Pair this with the SQL section to find the offending scan.
- log file sync high - commits waiting on redo to be written. Look at commit frequency and redo log device latency.
- gc buffer busy / gc cr high - RAC interconnect or block contention across instances.
- enq: TX - row lock contention - application locking, not an infrastructure problem.
The key habit: do not just note the top wait, note what percentage of DB Time it represents. A wait that is 4 percent of DB Time is noise. A wait that is 68 percent of DB Time is your incident.
Step 5: SQL Statistics, mapping waits to SQL
Once you know the dominant wait, the SQL Statistics section names the suspect. AWR ranks SQL several ways, and which list you read depends on the wait you found in step four:
- SQL ordered by Elapsed Time - the default starting point. Total wall-clock time per statement.
- SQL ordered by CPU Time - when the instance was CPU bound.
- SQL ordered by Buffer Gets - when logical reads spiked, as in the 02:14 case.
- SQL ordered by Physical Reads - when I/O waits dominated.
Match the list to the wait. I had a buffer-gets spike, so I went straight to SQL ordered by Buffer Gets. One statement, a single SQL_ID, accounted for the overwhelming majority of gets in the window. That is your target. Grab its SQL_ID and pull its execution plan.
-- Pull the plan history for the offending SQL_ID from AWR
SELECT sql_id,
plan_hash_value,
TO_CHAR(timestamp, 'DD-MON HH24:MI') AS captured,
optimizer_cost
FROM dba_hist_sql_plan
WHERE sql_id = '&sql_id'
GROUP BY sql_id, plan_hash_value, timestamp, optimizer_cost
ORDER BY captured;
If you see two different PLAN_HASH_VALUE rows for the same SQL_ID around the incident, you have confirmed a plan flip. That was exactly the 02:14 root cause: the optimizer had switched from an index range scan to a full table scan after a stats refresh.
I pinned the good plan with a SQL Plan Baseline, then corrected the stale statistics that caused the flip. p95 on the checkout API dropped from 4.2 seconds back to 190 ms within one snapshot interval. Total time from opening the report to identifying the SQL_ID: under five minutes, because I read by importance instead of top to bottom.
Generating the report in 19c
Before you can read a report you need to generate one. In 19c the scripts live under the RDBMS admin directory. For a single instance, use awrrpt.sql. On RAC, where you almost always want one specific instance, use awrrpti.sql.
-- Single instance report (interactive) SQL> @?/rdbms/admin/awrrpt.sql -- RAC: target a specific instance SQL> @?/rdbms/admin/awrrpti.sql -- The script asks for: report type (html/text), -- number of days, begin snap id, end snap id, and filename.
To choose the right snapshot IDs, list the recent snapshots first so you can pick a window that brackets the incident tightly rather than a generic "last hour".
-- Find snapshot IDs around the time of the problem
SELECT snap_id,
instance_number,
TO_CHAR(begin_interval_time, 'DD-MON HH24:MI') AS snap_start
FROM dba_hist_snapshot
WHERE begin_interval_time > SYSDATE - 1
ORDER BY snap_id;
What NOT to do with an AWR report
Most wasted hours in AWR analysis come from a handful of repeatable mistakes. Here is the contrast I teach every junior DBA on my team.
Do NOT do this
- Read the report top to bottom and get lost in section 2
- Chase the buffer hit ratio toward 99 percent as a goal
- Generate a 24-hour report for a 5-minute spike
- Look only at the top wait, ignoring its share of DB Time
- Fix a SQL without confirming the plan actually changed
Do this instead
- Jump to Top 10 Foreground Wait Events first faster
- Treat efficiency ratios as a sanity glance only
- Bracket the incident with a 30 to 60 minute window
- Read the percentage of DB Time, not just the rank
- Confirm a plan flip via PLAN_HASH_VALUE before acting
A common trap on RAC: pulling the global report and missing that only one instance was sick. On a three-node cluster, instance 2 can be on fire while 1 and 3 idle, and the aggregate looks merely "a bit busy". When the symptom is on one node, generate awrrpti.sql for that instance specifically. I have watched engineers tune the wrong instance for an hour because they read the cluster-wide numbers.
Frequently asked questions
How do you read an Oracle AWR report in 19c quickly?
Skip the top-to-bottom read. Start with the header to confirm the snapshot window and DB Time, then jump straight to the Top 10 Foreground Wait Events. That section tells you where time was actually spent. Trace the biggest wait back to the Top SQL section, and you have your suspect in under five minutes.
What is the first section to check when you read an Oracle AWR report in 19c?
Check the report header and the DB Time figure first. The header confirms the instance, the snapshot interval, and the elapsed time. DB Time divided by elapsed time tells you roughly how many sessions were active. If DB Time is far higher than your CPU count, the instance was overloaded during that window.
What is a healthy DB Time to Elapsed Time ratio?
There is no single magic number, but a useful rule is to compare average active sessions against your CPU core count. If DB Time divided by elapsed time stays near or below the number of CPU cores, the instance had headroom. When it runs several times higher than the core count, sessions were queuing and the database was CPU or wait bound.
How long should the AWR snapshot window be?
Keep the window tight around the problem. A one-hour report that spans both a quiet period and a spike will average the spike away and hide it. For a sharp incident I generate a report covering only the two or three snapshots around the slowdown, often a 30 to 60 minute window, so the wait profile reflects the real event.
Is AWR free to use in Oracle 19c?
No. AWR, ASH, and the related advisors are part of the Oracle Diagnostics Pack, which is a separately licensed option on Enterprise Edition. Running awrrpt.sql on an unlicensed instance is a license violation. If you do not hold the pack, use Statspack instead, which is free but collects less detail than AWR.
Want my AWR triage cheat sheet?
I am putting together a one-page printable version of this 6-step reading order with the red-flag thresholds.
Comment the keyword below and I will send it over.
Comment AWR
No comments:
Post a Comment