Mastering Oracle SQL Execution Plans: From Basics to Advanced Techniques

1. Overview of Execution Plans

An execution plan describes how Oracle will access tables and indexes to satisfy a SQL statement. The optimizer evaluates possible access paths and selects the one with the lowest estimated cost, storing the chosen plan in the Shared Pool for fast reuse.

2. Role of Statistics

Statistics such as NUM_ROWS, BLOCKS and LAST_ANALYZED enable the optimizer to estimate costs accurately. Oracle collects statistics automatically during off‑peak windows (default weekdays 22:00 and weekends 06:00) but can also collect them manually or for specific partitions.

exec dbms_stats.gather_table_stats(
  ownname => 'LJB',
  tabname => 'RANGE_PART_TAB',
  partname => 'p_201312',
  estimate_percent => 10,
  method_opt => 'for all indexed columns',
  cascade => TRUE
);

If a newly created table lacks statistics, Oracle performs dynamic sampling (level 2) during parsing to obtain temporary information, which disappears once statistics are gathered.

3. Methods to Retrieve Execution Plans

EXPLAIN PLAN FOR …

– stores the plan in a table viewable with DBMS_XPLAN.DISPLAY. SET AUTOTRACE ON – prints the plan and statistics after query execution. ALTER SESSION SET statistics_level = ALL – forces the optimizer to generate detailed statistics and a plan. DBMS_XPLAN.DISPLAY_CURSOR – shows the plan for the most recent cursor.

Event 10046 TRACE – captures a detailed trace file containing the plan.

Running @?/rdbms/admin/awrsqrpt.sql with appropriate snapshots and SQL_ID to generate a report.

4. Types of Execution Plans

Plans can be classified as:

Single (stand‑alone) plans – a parent‑child hierarchy where operations are executed from the deepest child (e.g., index access) up to the root.

Join (union) plans – multiple independent branches that are executed sequentially. Join plans further split into:

Non‑correlated joins (operations have no parent‑child relationship).

Correlated joins (e.g., NESTED LOOPS) where the row count of the driving table determines how many times the inner table is accessed.

FILTER joins – similar to NESTED LOOPS but the driver returns only distinct rows, reducing inner accesses.

UPDATE joins – behave like FILTER joins for DML statements.

CONNECT BY with FILTER – hierarchical queries where the number of starts reflects the depth of recursion.

5. Identifying Inefficient Plans

Key indicators of a bad plan include:

High Rows vs. Logical Reads ratio (e.g., 1 row requiring >1 000 logical reads).

Inaccurate cardinality estimates leading to sub‑optimal join orders.

Unnecessary type conversions that force full table scans.

Recursive calls that cause excessive depth.

Excessive table access counts or mismatched STARTS values.

Unexpected sorting operations increasing I/O.

Analyzing these metrics helps pinpoint missing or stale statistics, improper indexes, or query rewrites needed for performance improvement.

6. Summary

Understanding how Oracle builds and selects execution plans, maintaining up‑to‑date statistics, and using the appropriate method to view plans are essential steps for effective SQL tuning. Recognizing plan patterns—single, join, correlated, filter, and dynamic sampling—enables developers to diagnose inefficiencies and apply targeted optimizations.