Every Oracle performance engagement I’ve been part of has followed the same pattern when done correctly: start with AWR, form a hypothesis, validate with deeper tools, then act. The engagements that go badly almost always involve someone who acted before they understood — adding an index, changing a parameter, rewriting a query — based on intuition rather than evidence. AWR is Oracle’s built-in evidence machine. This post is about how to actually use it, not just generate the report.

Reading the AWR Report — The Sections That Actually Matter

An AWR report is 40-100 pages. Most of it is noise for any given performance problem. Learn to navigate directly to what matters.

Section 1: Load Profile

Load Profile                 Per Second   Per Transaction
~~~~~~~~~~~~                 ----------   ---------------
DB Time(s):                       12.5              0.02
DB CPU(s):                         8.2              0.01
Redo size:                   1,234,567           2,048
Logical reads:                 456,789             756
Block changes:                  12,345              20
Physical reads:                  5,678               9
Physical writes:                 1,234               2

DB Time is your primary metric. It’s the total Oracle work per second, across all sessions. Compare it to DB CPU — if DB Time >> DB CPU, you have a significant wait event problem. Sessions are spending most of their time waiting, not executing.

Section 2: Top 5 Timed Events (the most important section)

Top 5 Timed Events                             Avg wait   % DB
~~~~~~~~~~~~~~~~~~                              (ms)      time
db file sequential read                           0.5      45.2
CPU time                                            -      35.8
log file sync                                     8.2       8.1
db file scattered read                            2.1       5.3
buffer busy waits                                 1.8       3.2

This section tells you what your database spent its time doing during the AWR window. For each wait event, the question is: is this expected, and is it excessive?

db file sequential read = single-block reads, typically index lookups. Expected in OLTP workloads. Excessive if over 50% of DB time — suggests missing indexes or inefficient indexed access.

log file sync = commit waits. The session waits for the log writer to write redo to disk. High values (>5ms average, >10% DB time) indicate I/O issues on the redo log destination, or an application committing too frequently in small transactions.

buffer busy waits = sessions waiting for access to a buffer in the buffer cache that another session has locked. High values suggest hot blocks — a frequently accessed block being contended.

Finding the SQL That’s Causing the Problem

AWR’s SQL Statistics section shows your top SQL by various resource dimensions. Don’t just look at “Top SQL by Elapsed Time” — look at multiple dimensions:

sql

-- From the AWR repository: top SQL by elapsed time in last hour
SELECT s.sql_id,
       ROUND(s.elapsed_time_delta/1e6/s.executions_delta, 2) avg_ela_sec,
       s.executions_delta execs,
       ROUND(s.elapsed_time_delta/1e6, 2) total_ela_sec,
       ROUND(s.cpu_time_delta/1e6, 2) cpu_sec,
       s.buffer_gets_delta gets,
       ROUND(s.buffer_gets_delta/NULLIF(s.executions_delta,0),0) gets_per_exec
FROM dba_hist_sqlstat s
JOIN dba_hist_snapshot sn ON s.snap_id = sn.snap_id
    AND s.dbid = sn.dbid
    AND s.instance_number = sn.instance_number
WHERE sn.begin_interval_time > SYSDATE - 1/24
ORDER BY s.elapsed_time_delta DESC
FETCH FIRST 20 ROWS ONLY;

The gets_per_exec column is particularly revealing. A query with 10 million buffer gets per execution is doing a full table scan on a large table or performing a catastrophically inefficient join. A query with 100 million total elapsed time but only 100 executions averaging 1 second each is a different problem from a query with 100 million elapsed time spread across 10 million executions averaging 10ms each — both need investigation but in completely different ways.

The Three Performance Problems I See Most Often

Problem 1: Missing Index (or Wrong Index Being Used)

Classic symptom: AWR shows db file scattered read (multi-block reads = full table scans) as the top wait, with a specific SQL_ID in the top SQL by elapsed time. The query has a WHERE clause on a column with no index, or the optimizer is choosing a full scan because statistics are stale.

sql

-- Check if statistics are fresh
SELECT owner, table_name, last_analyzed, num_rows, stale_stats
FROM dba_tab_statistics
WHERE owner = 'MY_SCHEMA'
ORDER BY last_analyzed NULLS FIRST;
-- Check execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));

ALLSTATS LAST shows you the actual rows processed at each step versus the optimizer’s estimate. A large discrepancy (estimate: 1 row, actual: 1,000,000 rows) tells you the optimizer made a bad cardinality estimate, which usually means stale statistics or a histogram mismatch.

Problem 2: Commit Storm

An application that commits after every single row insert instead of batching commits generates enormous redo volume and log file sync waits. This is one of the most common performance antipatterns in applications migrated from non-Oracle databases.

AWR signal: high log file sync wait, very high redo size per second in the load profile, high “user commits” rate relative to “user calls.”

The fix is always in the application — batch your commits. Committing every 100 or 1000 rows instead of every row can improve insert performance by 10x or more. This is not a database tuning problem; it’s an application design problem.

Problem 3: Row-by-Row Processing (Cursor Loops)

PL/SQL code that fetches rows one at a time and processes them individually — the classic “slow by slow” antipattern. AWR shows high CPU time, high logical reads, high “parse count” if the SQL inside the loop doesn’t use bind variables.

sql

-- Bad: row-by-row
FOR rec IN (SELECT * FROM orders WHERE status = 'PENDING') LOOP
    UPDATE orders SET processed_date = SYSDATE
    WHERE order_id = rec.order_id;
    COMMIT;  -- Double problem: committing inside the loop too
END LOOP;
-- Good: set-based
UPDATE orders
SET processed_date = SYSDATE
WHERE status = 'PENDING';
COMMIT;

The set-based version executes one SQL statement. The row-by-row version executes N SQL statements for N rows. On a table with 100,000 pending orders, the difference is astronomical.

SQL Plan Management — Locking Good Plans

When a good execution plan changes for a bad reason (optimizer statistics refresh, bind variable peeking, database upgrade), you want to be able to lock the previous good plan without changing application code.

SQL Plan Management (SPM) does exactly this:

sql

-- Capture the good plan into SQL Plan Baseline
DECLARE
    l_plans INTEGER;
BEGIN
    l_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
        sql_id => '&good_sql_id',
        plan_hash_value => &good_plan_hash
    );
END;
/
-- Verify the baseline
SELECT sql_handle, plan_name, enabled, accepted, fixed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%your_query_signature%';

Once a SQL Plan Baseline exists for a statement, the optimizer will only use execution plans that are in the baseline (unless it finds a new plan that provably performs better). This is your insurance against plan regressions after patches, statistics refreshes, or upgrades.