Oracle AWR Reports Explained: Performance Tuning Without Guessing

What AWR Actually Is

AWR is Oracle's built-in performance repository. Every hour (by default), Oracle snapshots key performance metrics and stores them. AWR reports compare two snapshots, showing you exactly what happened in that window. For production performance issues, AWR is your first call, not your last.

Generating an AWR Report

-- As SYSDBA:
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

-- Or for a specific snapshot range:
SELECT snap_id, begin_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id DESC FETCH FIRST 10 ROWS ONLY;

Choose HTML format when possible—it's much easier to navigate than text.

Reading the Load Profile: Start Here

The Load Profile section tells you throughput: transactions per second, logical reads, physical reads, redo size. Compare these numbers to a baseline (yesterday, same time last week). A spike in physical reads with unchanged logical reads? Your buffer cache is too small. Sudden redo size increase? Somebody's running large DML.

Top 5 Wait Events: What's Actually Slowing You Down

This section is where most tuning happens. The top waits tell you where Oracle is spending its time waiting. Common findings and fixes:

• db file sequential read: Single-block reads (index lookups hitting disk). Increase buffer cache, or the index is no longer selective enough.
• db file scattered read: Multi-block reads (full table scans). Either add indexes or increase db_file_multiblock_read_count.
• log file sync: Commits waiting for redo log flush. Consider commit batching, faster storage for redo logs, or async commits.
• enq: TX - row lock contention: Row-level locking. Find the blocking session and the hot table.
• CPU time: If CPU is in the top 5, you're CPU-bound. Look at Top SQL for the culprits.

Top SQL Section: Your Action List

Top SQL by Elapsed Time, Buffer Gets, and CPU Time are your three go-to sections. A query appearing in all three is your highest-priority target. For each SQL_ID:

-- Get the full text:
SELECT sql_text FROM dba_hist_sqltext WHERE sql_id = 'your_sql_id';

-- Get the execution plan:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('your_sql_id'));

Parsing Statistics: When They Matter

High hard parse counts (vs. soft parses) indicates application-level connection pooling or bind variable issues. If your app is building SQL strings dynamically instead of using bind variables, every unique SQL string is a hard parse—expensive and not cached. This is almost always an application architecture issue.

Instance Efficiency Percentages

These should all be above 95% on a healthy system:

• Buffer Hit %: Reads served from memory vs. disk. Under 95% means your SGA is undersized.
• Redo NoWait %: Redo log contention. Should be 100%. If not, add redo log groups.
• Library Hit %: SQL reuse from shared pool. Under 90% means hard parse problems.

From Report to Fix: A Process

1. Start with Top 5 Waits
2. Cross-reference with Top SQL
3. Pull execution plan for top offenders
4. Check statistics freshness: SELECT table_name, last_analyzed FROM dba_tables WHERE owner = 'SCHEMA'
5. Compare buffer cache hit rates to your baseline
6. Fix one thing at a time. Measure before and after.