Understanding Oracle AWR Reports and Key Performance Metrics

When encountering performance problems in Oracle databases, the first step is to export an AWR (Automatic Workload Repository) report. AWR is a built‑in script that periodically snapshots all runtime data; DBAs can extract portions of this data for analysis to identify the scripts causing performance degradation. After installing Oracle, the script is typically available in the DBA's home directory and can be run from sqlplus with the @awrrpt command, producing both TXT and HTML outputs.

The AWR feature was introduced in Oracle 10g. It compares statistics from two snapshots to generate a multi‑section report. The following sections are explained in detail.

Workload Repository Report

DB Time excludes background processes. If DB Time is far smaller than Elapsed Time, the database is largely idle. DB Time = CPU time + non‑idle wait time, representing the server time spent on database computation and waiting.

An example shows a 79‑minute interval with 11 minutes of DB Time, 8 logical CPUs, and an average CPU utilization of about 2%, indicating very low system load.

Another example on an AIX server with four dual‑core CPUs (8 logical CPUs) calculates CPU usage percentages from DB Time versus total available CPU minutes, illustrating how to assess load.

From the AWR report, the Elapsed Time and DB Time fields give a quick view of database load.

For batch workloads, it is crucial to select a snapshot interval that captures the period of interest; otherwise the analysis may be meaningless.

Cache Sizes

Shows the size of each SGA component after any AMM changes, useful for comparing against initial parameter values.

Shared Pool consists of the Library Cache and Dictionary Cache. The Library Cache stores recently parsed SQL/PLSQL/Java objects, while the Dictionary Cache stores recently referenced data‑dictionary entries. Cache misses in these areas are more expensive than in the Buffer Cache, so the Shared Pool should be sized to keep frequently used objects cached.

Load Profile

Displays overall database load and should be compared with a baseline. High values for Logons (>1‑2 per second), Hard Parses (>100 per second), or total Parses (>300 per second) may indicate contention.

Redo size : bytes of redo generated per second, indicating data‑change frequency.

Logical reads : logical blocks read per second/transaction.

Block changes : blocks modified per second/transaction.

Physical reads : physical blocks read per second/transaction.

Physical writes : physical blocks written per second/transaction.

User calls : calls per second/transaction.

Parses : total parses (soft + hard); soft parses >300/s suggest low application efficiency.

Hard parses : indicate low SQL reuse.

Sorts : sorts per second/transaction.

Logons : logons per second/transaction.

Executes : SQL executions per second/transaction.

Transactions : transactions per second.

Blocks changed per Read : proportion of logical reads that modify blocks.

Recursive Call : percentage of recursive calls.

Rollback per transaction : rollback rate, high values may indicate excessive undo activity.

Rows per Sort : rows sorted per sort operation.

Oracle Hard and Soft Parses

SQL processing involves syntax check, semantic check, parsing (producing a parse tree and execution plan), and execution. Soft parses reuse an existing parse tree from the Library Cache; hard parses create a new one, which is more CPU‑intensive and should be minimized.

Instance Efficiency Percentages

These metrics include Buffer Hit Ratio, Library Hit Ratio, and other efficiency indicators for OLTP vs. OLAP workloads. For OLTP, Buffer Hit Ratio should exceed 90%; Buffer Nowait should be >99%; low Buffer Hit Ratio may signal memory pressure.

Shared Pool Statistics

Memory Usage % should stay between 75%‑90% for a stable system. SQL with executions >1 indicates reuse; low values suggest the need for bind variables.

Top 5 Timed Events

The report lists the five most time‑consuming wait events. Analyzing these events (e.g., Buffer Busy Wait, I/O waits, LATCH waits) helps prioritize tuning actions. CPU time should appear near the top; otherwise the system spends most of its time waiting.

The author concludes the tutorial and mentions testing a WeChat mini‑program among peers.