Understanding Oracle Performance Tools and Their Application in Database Optimization

Author Introduction

Liang Jingbin, deputy director of Fufu Research Institute and a four‑star internal trainer, is a well‑known Oracle database expert with extensive experience in database optimization and training.

Overall Learning Path

The article starts by emphasizing a holistic approach to SQL optimization, then introduces the major Oracle performance tools, analyzes their characteristics, and finally presents case studies and a summary.

Types of Optimization Scenarios

Two categories are defined: simple scenarios (e.g., a single slow SQL that can be fixed by adding an index) and complex scenarios (e.g., a query remains slow due to host resource exhaustion or high execution frequency).

Local Analysis Tool

Execution‑plan analysis is highlighted as the most important technique for pinpointing the root cause of a slow SQL.

Overall Optimization Tools

Four primary Oracle reports are introduced: AWR (Automatic Workload Repository), ASH (Active Session History), ADDM (Automatic Database Diagnostic Monitor), and AWRDD (AWR Difference Report). A fifth report, AWRSQRPT, is also mentioned for detailed SQL‑level diagnostics.

How to Obtain the Reports

Each report can be retrieved in two ways: (1) executing the corresponding SQL*Plus script, and (2) calling the DBMS_WORKLOAD_REPOSITORY package.

Examples of script execution:

@?/rdbms/admin/awrrpt.sql

@?/rdbms/admin/ashrpt.sql

@?/rdbms/admin/addmrpt.sql

@?/rdbms/admin/awrddrpt.sql

Examples of package calls:

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

Select output from table(dbms_workload_repository.ash_report_html(dbid, inst_num, l_btime, l_etime));

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

Key Metrics of AWR

Important indicators include DB Time, load_profile, efficiency percentages (e.g., Buffer Hit, Library Hit, Soft Parse), Top 5 Events, SQL Statistics, and Segment Statistics.

Key Metrics of ASH

ASH focuses on linking specific SQL statements with wait events, allowing identification of problematic sessions.

Key Metrics of ADDM

ADDM provides high‑level recommendations (overall) and detailed SQL‑level advice (local), presented as FINDING 1, FINDING 2, etc.

Key Metrics of AWRDD

AWRDD compares two time periods, highlighting differences in load profile, wait events, and top SQL statements.

Key Metrics of AWRSQRPT

AWRSQRPT reveals execution‑plan details for a specific SQL_ID, useful when multiple plans exist.

Case Studies

Several real‑world examples illustrate how the reports help diagnose and resolve performance issues:

Parallel‑wait case: excessive parallelism caused PX Deq waits; solution was to remove parallel attributes and schedule heavy parallel jobs at night.

Hot‑block contention case: gc buffer busy identified as the bottleneck; workload was redistributed across nodes.

Log‑wait case: high transaction count with small per‑transaction work indicated missing batch commits; fixing commit placement reduced log file switch and sync waits.

IO‑bound case in Xinjiang: high AV RD(MS) values revealed storage bottlenecks, resolved by improving storage.

GC buffer busy case in Zhejiang: segment‑by‑global‑cache‑buffer‑busy analysis led to table pruning, partitioning, and workload segregation.

Summary

The article concludes that mastering Oracle performance reports—understanding how to obtain them, which metrics to focus on, and how to combine insights from multiple reports—enables DBAs to efficiently diagnose and resolve both simple and complex performance problems.