Understanding Oracle SQL Execution Plans and Statistics for Performance Optimization

The article continues a series on SQL optimization by focusing on execution plans, describing what an execution plan is, how Oracle chooses the plan with the lowest cost, and why reading the plan is essential for performance tuning.

An execution plan represents the chosen access path for a SQL statement; Oracle evaluates possible paths and selects the one with the smallest estimated cost, storing it in the Shared Pool for reuse.

Statistics are the key to accurate cost estimation. Oracle periodically gathers table and index statistics (default nightly) and stores values such as NUM_ROWS and BLOCKS, allowing the optimizer to quickly assess table size without full scans.

Statistics collection can be performed manually. For example:

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);

When a table lacks statistics, Oracle uses dynamic sampling (level 2) to collect information on‑the‑fly, as shown by enabling set autotrace on and observing the "dynamic sampling used for this statement" message.

There are six common ways to obtain an execution plan:

EXPLAIN PLAN FOR – stores the plan in a table for later display.

SET AUTOTRACE ON – prints the plan and statistics after query execution.

STATISTICS_LEVEL=ALL – forces the optimizer to generate detailed statistics.

DBMS_XPLAN.DISPLAY_CURSOR – retrieves the plan of the most recent cursor.

EVENT 10046 TRACE – captures a detailed trace of the execution.

awrsqrpt.sql – a script that generates a report based on snapshots and SQL_ID.

The article then distinguishes two plan structures:

Isolated (single) plans – a parent‑child hierarchy where execution proceeds from the deepest ID to the top (e.g., ID=3 → ID=2 → ID=1).

Join (union) plans – can be non‑correlated (independent branches executed sequentially) or correlated (driving row count determines how many times the inner branch runs). Examples of NESTED LOOPS, FILTER, UPDATE, and CONNECT BY with filtering are illustrated.

Practical tips for spotting inefficient SQL include:

Compare returned rows with logical reads; a high read‑to‑row ratio indicates a problem.

Ensure accurate cardinality estimates; mismatches lead to poor plan choices.

Watch for implicit type conversions that may prevent index usage.

Be cautious with recursive calls and their impact on plan depth.

Check the number of table accesses and the actual rows processed.

Observe whether sorting operations are required and their cost.

Summarizing the efficiency considerations, the article provides a visual checklist and emphasizes that understanding plan structure, statistics, and execution metrics is essential for effective SQL tuning.

Finally, the author bio introduces Liang Jingbin, a senior DBA and trainer, and promotes his new book "Harvest, Not Just SQL Optimization" with accompanying images.