Mastering Oracle AWR: How to Decode and Optimize Database Performance

What Is AWR?

Automatic Workload Repository (AWR) is Oracle's built‑in tool that automatically captures a wide range of performance statistics for a database at regular intervals (default one hour) and stores them as snapshots. Snapshots are retained for a configurable period (default one week) and then purged.

Why AWR Matters

Think of a dark room illuminated by many candles; AWR places enough "candles" (statistics) to light most of the room, making performance bottlenecks easier to locate compared with systems that provide few or no statistics.

How to Read an AWR Report

The most frequently used AWR report contains several logical sections.

Database Information – basic instance details.

Snapshot Information – start and end snapshot IDs and timestamps.

(1) Sessions – number of active sessions, useful for estimating concurrency.

(2) Cursors/Session – average open cursors per session.

(3) Elapsed – compares Elapsed time to DB Time; a large DB Time ≫ Elapsed indicates a busy system.

(4) DB Time – total time spent by user processes (CPU + wait time).

Specific meaning :

db time = cpu time + wait time (excluding idle wait, non‑background processes)

. This value is recorded in V$SYSTEM_EVENT and V$SYS_TIME_MODEL.

Aggregate data – AWR stores cumulative metrics. For example, if ten users each wait 30 seconds in one minute, the total wait time recorded is 300 seconds.

Example Calculations

Given sample values:

DB CPU = 6474.65
DB TIME = 10711.2
FG Wait Time = 1182.63

DB CPU + FG Wait Time accounts for only 71.5 % of DB Time, indicating that the remaining 28.5 % is spent in process‑queue time when many sessions compete for CPU.

Report Summary

Cache Sizes – shows buffer cache and shared pool sizes at the beginning and end of the snapshot interval.

Load Profile – per‑second and per‑transaction resource usage, including Redo size, Logical reads, Block changes, Physical reads/writes, User calls, Parses, Hard parses, Sorts, Logons, Executes, Transactions, % Blocks changed per read, Rollback % per transaction, Recursive Call %.

Instance Efficiency Percentages (Target 100 %) – metrics such as Buffer Nowait %, Redo NoWait %, Buffer Hit %, In‑memory Sort %, Library Hit %, Soft Parse %, Execute‑to‑Parse %.

Shared Pool Statistics – memory usage % and % SQL with executions > 1.

Top 5 Timed Events – displays the five events that consumed the most DB Time.

RAC Statistics (Only for RAC Environments)

Global Cache Load Profile – latency of inter‑node cache traffic.

Global Cache Efficiency Percentages – local + remote efficiency target 100 %.

Global Cache and Enqueue Services – workload characteristics and messaging statistics.

Global Cache Transfer Stats – % Busy indicates block contention between nodes.

Wait Events Statistics

Time Model Statistics – proportion of DB time spent in various operations.

Wait Class – categorises wait events (e.g., CPU, I/O, concurrency).

Wait Events – detailed list of the top wait events, including % Timeouts.

Background Wait Events – waits for background processes such as DBWR.

Operating System Statistics

Shows OS‑level CPU usage (%User, %Sys, %Idle) and the proportion of OS CPU consumed by the database (%Busy, %Busy CPU). Example calculations for an 8‑CPU system illustrate how to derive %Busy CPU = DB CPU / %Busy × 100.

SQL Statistics

Multiple ordered lists help identify the most expensive SQL statements:

SQL ordered by Elapsed Time – total execution time, CPU time, executions, Elap per Exec, % Total DB Time, SQL ID, Module, Text.

SQL ordered by CPU Time – total CPU consumption, % Total DB Time.

SQL ordered by Gets – logical reads (buffer gets) and their share of total.

SQL ordered by Reads – physical reads and per‑execution cost.

SQL ordered by Executions – frequency of execution, rows processed, rows per exec.

SQL ordered by Parse Calls – total parse (soft + hard) calls; high values may indicate missing bind variables.

SQL ordered by Sharable Memory – library cache memory usage.

SQL ordered by Version Count – number of cursor versions (re‑parses).

SQL ordered by Cluster Wait Time – only in RAC, shows inter‑node wait time.

Complete List of SQL Text – full text of all SQL statements referenced in the report.

Key formulas used in the analysis:

round(100*(1-:prse/:exe),2)   -- (Execute – Parse)/Execute × 100%

where

prse = select value from v$sysstat where name = 'parse count (total)'

and

exe = select value from v$sysstat where name = 'execute count'

.

Instance Activity Statistics

Provides a wide range of per‑instance counters, useful for RAC environments where each instance must be examined individually.

IO Statistics

Tablespace IO Stats – reads, writes, average read/write latency, blocks per read, buffer waits.

File IO Stats – physical I/O at the file level.

Advisory Statistics

Buffer Pool Advisory – recommended buffer pool size, size factor, estimated physical reads.

PGA Memory Advisory – suggested PGA target, size factor, estimated extra reads/writes, estimated PGA cache hit %.

Shared Pool Advisory – optimal shared pool size, memory object count, estimated load time savings.

SGA Target Advisory – recommended SGA size and its impact on DB Time and physical reads.

Latch Statistics

Shows latch activity, including get requests, no‑wait requests, and miss percentages; low miss rates are desirable.

Segment Statistics

Segments by Logical Reads – identifies objects with high logical I/O.

Segments by Physical Reads – identifies objects with high physical I/O.

Segments by Buffer Busy Waits – highlights hot blocks causing contention.

Segments by Row Lock Waits – includes row‑level lock waits and index‑split waits.

Other Considerations

SQL statements are only recorded in AWR after they finish execution. If a statement runs across the snapshot interval but does not complete, its resource consumption may be missing from the report, which can lead to incomplete analysis.

For further reading, see related Oracle performance topics (links omitted for brevity).