Oracle tuning with AWR: how to read the report and find the SQL that is costing you a server

When a system on Oracle “gets slow”, the most common reaction is to look at server CPU, see it at 90 % and request a quote for a bigger box. In most of the cases we have handled, that new server would have been wasted: CPU sat at 90 % because a handful of SQL statements performed millions of logical reads they never needed. The AWR report tells you exactly which ones. This guide explains how to read it with judgment.

What AWR is and what you need to use it

The Automatic Workload Repository takes snapshots of instance state every hour by default and keeps them for eight days. An AWR report compares two snapshots and summarizes everything that happened in between: database time, waits, most expensive SQL, I/O per file, memory statistics.

Requirements:

• Oracle Enterprise Edition with the Diagnostics Pack licensed. AWR and ASH belong to that pack. On Standard Edition, or without the pack, use Statspack (free, less detailed) or the V$ views directly. Querying DBA_HIST_* without a license is a contract violation that Oracle does audit.
• Snapshots covering the problem window. If the issue happens from 10:00 to 10:40, a 09:00–11:00 report dilutes the signal. Take manual snapshots with DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT before and after reproducing the problem.

To generate the report:

-- As SYS or a user with privileges on DBMS_WORKLOAD_REPOSITORY
@?/rdbms/admin/awrrpt.sql
-- Pick html, the snapshot range and the file name

Rule of thumb: a 30-to-60-minute report over the problem window is worth more than a 24-hour one. A daily average hides the peak you care about.

The five sections to read, in order

1. Load Profile: how much work per second?

It is at the top. Compare DB Time(s) per second with DB CPU(s). With 8 cores and DB CPU per second at 7.5, the database is saturating CPU. If DB Time is 40 and DB CPU is 6, the rest of the time the database is waiting for something (I/O, locks, network), not processing.

Also look at Logical reads and Physical reads per second. Hundreds of thousands of logical reads per second with few transactions usually means inefficient plans: full scans where index access belongs, or indexes returning too many rows.

2. Top 10 Foreground Events: what is the database waiting on?

This table is the heart of the report. Each row is a wait class with its share of DB Time. How to read the common ones:

Event	What it usually means	Where to look next
DB CPU	The database is processing. If it dominates, the problem is CPU-hungry SQL: excessive logical reads, PL/SQL functions in the WHERE, large sorts.	SQL ordered by CPU / Gets
db file sequential read	Single-block reads, typical of index access. High share plus high average time (>10 ms) points to slow storage.	SQL ordered by Reads, Tablespace IO
db file scattered read	Multi-block reads: full table scans. If high, queries are sweeping whole tables.	SQL ordered by Reads
direct path read	Parallel full scans or large tables bypassing the buffer cache. Normal in reporting, suspicious in OLTP.	SQL ordered by Reads
log file sync	Commits wait for redo to hit disk. Average >5 ms means slow redo disks; a huge count means row-by-row commits in a loop.	Instance Activity (user commits), redo I/O
enq: TX - row lock contention	Sessions blocked by other sessions. Application design, not infrastructure.	Segments by Row Lock Waits
buffer busy waits	Several sessions fighting over the same block. Hot insert tables or indexes on sequences.	Segments by Buffer Busy Waits
library cache lock / cursor: pin S wait on X	Excessive parsing: SQL without bind variables, or DDL at peak time.	Parse statistics, SQL ordered by Parse Calls

If DB CPU and logical reads dominate, the path is SQL. If I/O waits with high average times dominate, storage may be the cause, but first check whether the I/O is necessary: a bad plan generates I/O that no fast disk will compensate for.

3. SQL ordered by Elapsed Time, CPU, Gets and Reads

This is where “5 % of the SQL burns 80 % of the database” lives. Sort mentally by what dominated the wait events:

• CPU dominated: SQL ordered by Gets (logical reads) and by CPU Time.
• I/O dominated: SQL ordered by Reads (physical reads).
• Users complain about slow screens: SQL ordered by Elapsed Time, and watch Elapsed per Exec. A statement executed 50,000 times at 0.1 s weighs more than one executed once in 30 s.

Note the SQL Id of the three or four statements leading the lists. They are almost always the same ones across several lists. Then get the plan the database actually used, not the estimate:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));
-- If the cursor is no longer in memory:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));

What to look for in the plan: TABLE ACCESS FULL on large tables with selective filters, NESTED LOOPS with millions of iterations, HASH JOIN spilling to disk (TempSpc), and large gaps between E-Rows (estimated) and A-Rows (actual), which indicate stale statistics or missing histograms.

4. Segment Statistics: which objects suffer?

Segments by Logical Reads, by Physical Reads, by Row Lock Waits and by Buffer Busy Waits show which tables and indexes carry the load. An index topping logical reads on a small table is usually fragmented or the wrong index for the access pattern.

5. Instance Efficiency and Parse Statistics

A low Execute to Parse % (under 60–70 %) and Soft Parse % below 95 % mean the application sends SQL with literals instead of bind variables. That fills the shared pool, triggers library cache waits and burns CPU on parsing. The fix belongs in the application; in the meantime, CURSOR_SHARING=FORCE is a patch with side effects worth measuring before applying it in production.

ASH: when the problem lasts minutes, not hours

AWR averages an hour. If the slowdown lasts five minutes, ASH (Active Session History) gives you per-second samples of what every active session was doing:

SELECT sql_id, event, COUNT(*) samples
FROM   v$active_session_history
WHERE  sample_time BETWEEN TO_DATE('2026-10-15 10:05','YYYY-MM-DD HH24:MI')
                       AND TO_DATE('2026-10-15 10:15','YYYY-MM-DD HH24:MI')
GROUP  BY sql_id, event
ORDER  BY samples DESC
FETCH FIRST 15 ROWS ONLY;

The ashrpt.sql report does the same with formatting. For a point incident, ASH answers in ten minutes what AWR takes an hour to show.

The quick wins that usually show up

In order of frequency across the diagnostics we have run:

1. Stale statistics or missing histograms on tables with skewed distributions. Fix: DBMS_STATS.GATHER_TABLE_STATS with a proper METHOD_OPT and, where applicable, incremental statistics on partitioned tables.
2. Missing index for a highly selective predicate. Before creating it, check how many indexes the table already has: each one makes INSERT/UPDATE more expensive.
3. Unstable plans: the same statement alternates between a good and a bad plan depending on bind values (bind peeking). Fix: SQL Plan Baselines to pin the right plan without touching code.
4. Undersized SGA: low Buffer Cache Hit % with free memory on the server. Check Buffer Pool Advisory and SGA Target Advisory in the same report; Oracle tells you how much each increment would gain.
5. Row-by-row commits in batch jobs. Instance Activity Stats shows user commits; tens of thousands per minute in a nightly job means the change is in the PL/SQL (commit every N rows).
6. PL/SQL in the WHERE: a function called for every evaluated row. Function result cache or a rewrite as a JOIN usually cuts the cost by orders of magnitude.

What not to do

• Do not raise parameters at random. Increasing SGA_TARGET without advisory evidence, or setting OPTIMIZER_INDEX_COST_ADJ, can make other plans worse.
• Do not reach for hints first. A hint freezes an optimizer decision forever, even as data changes. Prefer correct statistics and baselines.
• Do not restart the instance “to clean up”. You lose the buffer cache, the shared pool and the evidence. If the problem is recurring, it comes back in an hour.
• Do not buy hardware without an AWR report of the problem window showing I/O waits with high times and SQL already optimized. It is the only valid technical justification.

How to measure that it worked

Take the AWR for the same window (same weekday, same hour) before and after the change. Compare total DB Time, the top waits and Elapsed per Exec of the statements you touched. A serious tuning report delivers those three numbers, not an impression that “it feels faster now”.

---

If your database shows performance symptoms and nobody has looked at an AWR of the problem window, the initial diagnosis takes three to five business days and usually ends with quick wins applied in the first week.