Mastering Oracle Performance: A Guide to AWR, ASH, ADDM, and More

Introduction

The article presents a systematic approach to Oracle database performance optimization, starting with an overview of the five major performance tools and then diving into their characteristics, usage methods, and practical examples.

Performance Tools Overview

Two categories of optimization scenarios are defined: simple scenarios (e.g., a single slow SQL that can be fixed by adding an index) and complex scenarios (e.g., overall system resource exhaustion that affects all queries). Corresponding tools are mapped to these scenarios.

Tool Details

1. AWR (Automatic Workload Repository)

AWR provides a comprehensive snapshot of database performance over a time interval. It includes metrics such as DB Time, load_profile, efficiency percentages, top‑5 events, SQL statistics, and segment statistics.

Key metrics:

DB Time – compares elapsed time multiplied by CPU count with DB Time to assess system pressure.

load_profile – shows overall parameters like Redo size and Transactions per second.

efficiency percentages – hit ratios for Buffer, Library, Soft Parse, etc.

top‑5 events – identifies the most time‑consuming wait events.

SQL statistics – lists top SQL statements by execution time.

segment statistics – helps locate hot segments for targeted cleanup.

2. ASH (Active Session History)

ASH captures wait events and the associated SQL statements at a fine granularity. It is useful for pinpointing which SQLs are causing specific waits.

3. ADDM (Automatic Database Diagnostic Monitor)

ADDM analyzes AWR data and provides actionable recommendations, both at the overall configuration level and for individual SQL statements.

4. AWRDD (AWR Difference Report)

AWRDD compares two AWR snapshots to highlight changes in load profile, wait events, and top SQL statements across different time periods.

5. AWRSQRPT (SQL Query Report)

AWRSQRPT drills down into execution plan details that are not visible in AWR/ASH, showing plan statistics, multiple execution plans, and related SQL IDs.

How to Obtain the Reports

Each report can be generated either by running the corresponding SQL*Plus script or by invoking the DBMS_WORKLOAD_REPOSITORY package directly.

AWR example:

Select output from table(dbms_workload_repository.awr_report_html(v_dbid, v_instance_number, v_min_snap_id, v_max_snap_id));

ASH example:

Select output from table(dbms_workload_repository.ash_report_html(v_dbid, v_instance_number, v_begin_time, v_end_time));

ADDM example:

DBMS_ADVISOR.create_task(advisor_name => 'ADDM', task_name => 'MYADDM', task_desc => 'MYADDM');

Scripts such as @?/rdbms/admin/awrrpt.sql, @?/rdbms/admin/ashrpt.sql, and @?/rdbms/admin/addmrpt.sql can also be executed directly in SQL*Plus.

Key Points for Each Report

For AWR, focus on DB Time, load_profile, efficiency percentages, top‑5 events, SQL statistics, and segment statistics. For ASH, examine the mapping between SQL IDs and wait events. For ADDM, read the FINDING sections to get configuration and SQL‑level advice. For AWRDD, compare load profiles, wait events, and top SQL across snapshots. For AWRSQRPT, review plan statistics and detect multiple execution plans.

Case Studies

Several real‑world cases illustrate how the reports guide optimization:

Parallel wait issue: AWR top‑5 events revealed excessive PX Deq waits; removing parallelism from tables and scheduling heavy parallel jobs at night resolved the bottleneck.

Hot block contention: ASH and AWR identified gc buffer busy as the dominant wait; separating workloads across nodes eliminated contention.

Log file switch overload: High transaction count with low per‑transaction work indicated missing batch commits; fixing commit logic reduced log file sync and checkpoint waits.

I/O bottleneck in a regional system: AWR showed AV RD(MS) > 7, confirming severe I/O latency; upgrading storage resolved the issue.

Segment‑level hotspot: Segment statistics highlighted a heavily accessed table; partitioning and pruning the table improved performance.

Conclusion

Understanding and correctly using the five Oracle performance reports—AWR, ASH, ADDM, AWRDD, and AWRSQRPT—covers both the overall health check (similar to a medical examination) and detailed SQL‑level diagnostics. Combining these tools enables DBAs to pinpoint root causes, prioritize fixes, and achieve substantial performance gains.