Explain Plan and Query Optimization

EXPLAIN PLAN shows you the strategy Oracle's optimiser chose to execute your query — which tables it accesses first, which indexes it uses, how it joins them, and how much work each step costs. Reading a plan is the single most useful tuning skill in Oracle.

This chapter goes deeper than the introduction in Performance — covering the full plan tree, every common operation, hints, AUTOTRACE, SQL Monitoring, and how to read plans like a tuning specialist.

Generating an Explain Plan

The classic two-step approach:

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 50;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

This stores the plan in the PLAN_TABLE table and then formats it for display. The output is the execution plan in tree form.

If you have AUTOTRACE enabled, you get the plan automatically after every query:

SET AUTOTRACE ON EXPLAIN
SELECT * FROM employees WHERE department_id = 50;
-- query runs, plan displayed

For more detail, use DBMS_XPLAN formatting options:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ADVANCED'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'BASIC +PARTITION +PARALLEL'));

Reading a Plan

A plan output looks like this:

| Id | Operation                    | Name           | Rows | Bytes | Cost  |
|  0 | SELECT STATEMENT             |                |   45 |  3105 |   3   |
|  1 |  NESTED LOOPS                |                |   45 |  3105 |   3   |
|  2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES      |   45 |  2880 |   2   |
|* 3 |    INDEX RANGE SCAN          | EMP_DEPT_IDX   |   45 |       |   1   |
|  4 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS    |    1 |     5 |   1   |
|* 5 |    INDEX UNIQUE SCAN         | DEPT_PK        |    1 |       |   0   |

Read it like a tree, leaves first:

• Steps are numbered by Id
• Indentation shows nesting — deeper indent = inner operation
• Children execute before parents
• Steps with * have predicates (filtering conditions) shown below the table
• Rows is the optimiser's estimate of how many rows that step produces
• Bytes is the estimated data volume
• Cost is a relative measure of work (CPU + I/O), not seconds

Execution order for the example above:

1. Step 3 — Use the EMP_DEPT_IDX index to find employees in department 50
2. Step 2 — Fetch the actual employee rows from the EMPLOYEES table
3. Step 5 — For each employee, look up the department by primary key
4. Step 4 — Fetch the department row
5. Step 1 — Nested loops produce the joined result
6. Step 0 — Final result returned

Reading bottom-up at the same indentation level, then up to the parent, gives you the order operations actually happen.

Cardinality and Cost — What the Numbers Mean

Rows (cardinality) is the optimiser's estimate based on statistics. Two implications:

1. The optimiser's choices are driven by these estimates. If they're wrong (e.g., stale statistics), the chosen plan can be terribly wrong.
2. Estimates that differ wildly from reality are the #1 cause of bad plans. Check actual vs estimated rows.

Compare estimated to actual:

SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM employees WHERE department_id = 50;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

This shows E-Rows (estimated) and A-Rows (actual) side by side. A 10× discrepancy is a red flag; 100× is critical.

| Id | Operation               | E-Rows | A-Rows |
|  0 | SELECT STATEMENT        |        |    102 |
|* 1 |  TABLE ACCESS FULL EMP  |    50  |    102 |   ← estimate was 2× off

When estimates are wrong:

• Stale statistics → BEGIN DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMPLOYEES'); END;
• Skewed data with no histogram → gather statistics with METHOD_OPT => 'FOR COLUMNS dept_id SIZE 254'
• Bind variable peeking issues → consider adaptive cursors
• Correlated columns → use extended statistics

Common Operations You Need to Recognise

TABLE ACCESS FULL

Reads every block of a table.

TABLE ACCESS FULL  | EMPLOYEES

Good when: small tables, or > ~5% of the table needs to be read. Bad when: huge table, narrow filter — usually means a missing index.

INDEX UNIQUE SCAN

Looks up a single row by a unique key.

INDEX UNIQUE SCAN  | EMP_PK

The optimal access method. Used for primary key lookups and unique indexes with = predicates.

INDEX RANGE SCAN

Returns a range of values from a B-tree index.

INDEX RANGE SCAN   | EMP_DEPT_IDX

Good for WHERE col BETWEEN ... AND ..., WHERE col = 'X' on non-unique indexes, WHERE col LIKE 'A%'.

INDEX FULL SCAN

Reads every entry of an index in order. Cheaper than a table scan because the index is smaller, plus the data comes back sorted.

INDEX FULL SCAN    | EMP_NAME_IDX

Used for ORDER BY on an indexed column when most or all rows are needed.

INDEX FAST FULL SCAN

Reads every entry of an index in physical order (unordered), much faster than INDEX FULL SCAN. Used when order doesn't matter and the index covers all needed columns.

INDEX FAST FULL SCAN | EMP_DEPT_IDX

Effectively "the index is a smaller table I can scan instead".

INDEX SKIP SCAN

Used on composite indexes when the leading column isn't in the WHERE clause but a later column is.

INDEX SKIP SCAN    | EMP_DEPT_JOB_IDX

Skips through the leading values, scanning sub-ranges for each. Useful when the leading column has low cardinality.

NESTED LOOPS

For each row in the outer set, look up matching rows in the inner.

NESTED LOOPS
  TABLE ACCESS  | EMP
  INDEX SCAN    | DEPT_PK

Best when: outer set is small AND inner has an index on the join key. Performance scales with outer × log(inner).

HASH JOIN

Build a hash table on one side, probe with the other.

HASH JOIN
  TABLE ACCESS FULL | EMP
  TABLE ACCESS FULL | DEPT

Best for joining two large sets. Cost is approximately (left size + right size), so it scales linearly with table sizes.

MERGE JOIN (SORT-MERGE)

Sort both sides, then walk through both in order matching them up.

MERGE JOIN
  SORT JOIN
    TABLE ACCESS FULL | EMP
  SORT JOIN
    TABLE ACCESS FULL | DEPT

Used when both sides are already sorted (e.g., from index access) or are too big for hash join's memory.

FILTER

A predicate applied after another operation, often a correlated subquery.

FILTER
  TABLE ACCESS FULL  | EMP
  TABLE ACCESS FULL  | DEPT (run once per emp row)

Subqueries that don't unnest become FILTERs — check for this if a subquery seems slow.

Other Operations to Recognise

Hints — Suggesting a Plan

Hints are comments that tell the optimiser to prefer a particular operation. They take the form /*+ HINT_NAME(params) */ and go right after the SELECT, INSERT, UPDATE, or DELETE keyword.

SELECT /*+ INDEX(e emp_dept_idx) */ *
FROM   employees e
WHERE  department_id = 50;

Common hints:

When to use hints:

• Production query is critical and you need a guaranteed plan
• Statistics are stale or temporarily unreliable
• Optimiser is making a wrong choice in a specific scenario

When NOT to use hints:

• "Just to be sure" — let the optimiser do its job
• To work around stale statistics — fix the statistics instead
• When you don't understand why the chosen plan is wrong

Hints lock you into a plan that may become wrong as data grows. Use sparingly.

AUTOTRACE

AUTOTRACE is a SQL*Plus / SQL Developer feature that shows the plan and runtime statistics for queries automatically.

SET AUTOTRACE ON                    -- query, plan, statistics
SET AUTOTRACE ON EXPLAIN            -- plan only, no statistics
SET AUTOTRACE TRACEONLY              -- statistics, suppress query output
SET AUTOTRACE OFF                    -- turn off

Statistics shown include:

• consistent gets — logical reads (block accesses in cache)
• physical reads — disk reads
• recursive calls — internal calls (parsing, dictionary lookups)
• sorts (memory) and sorts (disk) — sort operations

Logical reads are the primary tuning metric. Fewer logical reads = less work. Compare consistent gets between two query variants to find the better one.

SQL Monitoring

For long-running queries, SQL Monitoring shows actual progress in real time:

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => 'abc123xyz')
FROM   dual;

This requires the Tuning Pack license. It shows:

• Which operation is currently running
• How much CPU/IO time has been spent
• Estimated vs actual rows at each step
• Wait events
• Parallel execution servers (if any)

In Enterprise Manager / OEM Cloud Control, this view is graphical and live-updating — the gold standard for monitoring long queries.

Worked Example — Diagnosing a Slow Query

A report query is taking 8 minutes:

SELECT d.department_name, COUNT(*), SUM(e.salary)
FROM   employees e
JOIN   departments d ON e.department_id = d.department_id
WHERE  e.hire_date BETWEEN DATE '2020-01-01' AND DATE '2024-12-31'
GROUP BY d.department_name;

Step 1 — Get the actual plan with rowsource statistics:

SELECT /*+ GATHER_PLAN_STATISTICS */ ...
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

| Id | Operation                    | Name        | E-Rows | A-Rows | A-Time     |
|  0 | SELECT STATEMENT             |             |        |     27 | 8:12.45    |
|  1 |  HASH GROUP BY               |             |     20 |     27 | 8:12.45    |
|* 2 |   HASH JOIN                  |             | 50,000 | 350,000| 8:11.30    |
|  3 |    TABLE ACCESS FULL         | DEPARTMENTS |     27 |     27 | 0:00.01    |
|* 4 |    TABLE ACCESS FULL         | EMPLOYEES   | 50,000 | 350,000| 8:10.95    |

Step 2 — Spot the problems:

• Step 4: E-Rows 50,000 vs A-Rows 350,000 — estimate is 7× low
• Step 4: full table scan on EMPLOYEES taking 8 minutes
• No index on hire_date

Step 3 — Apply the fix:

-- Add an index on hire_date:
CREATE INDEX emp_hire_date_idx ON employees(hire_date);

-- Refresh statistics so the optimiser knows about the new index:
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMPLOYEES',
    method_opt => 'FOR COLUMNS hire_date SIZE 254');
END;
/

Step 4 — Verify:

| Id | Operation                          | A-Rows | A-Time |
|  0 | SELECT STATEMENT                   |     27 | 0:00.85 |
|  1 |  HASH GROUP BY                     |     27 | 0:00.85 |
|* 2 |   HASH JOIN                        | 350K   | 0:00.80 |
|  3 |    TABLE ACCESS FULL DEPARTMENTS   |     27 | 0:00.01 |
|  4 |    TABLE ACCESS BY INDEX ROWID EMP | 350K   | 0:00.55 |
|* 5 |     INDEX RANGE SCAN EMP_HD_IDX    | 350K   | 0:00.20 |

8 minutes → 0.85 seconds. The 7× cardinality miss disappeared once the optimiser had column histograms.

Quick Tuning Checklist

When a query is slow, ask in order:

1. Get a real execution plan (with GATHER_PLAN_STATISTICS)
2. Check E-Rows vs A-Rows — large discrepancies indicate stats problems
3. Look for full scans on large tables → missing or unusable index
4. Check pruning on partitioned tables → defeated by a function or implicit conversion
5. Inspect join order — leading table should be the most selective one
6. Look for sorts — SORT GROUP BY or SORT ORDER BY on huge datasets may benefit from an index
7. Check parallelism — large data warehouse queries should use it
8. Refresh statistics — GATHER_TABLE_STATS with histograms on skewed columns
9. Only then consider hints — and verify they actually changed the plan

Common Errors

Interview Corner

-- Look at all plans for this SQL_ID:
SELECT plan_hash_value, COUNT(*), AVG(elapsed_time/executions/1e6) AS avg_sec
FROM   v$sql
WHERE  sql_id = 'abc123'
GROUP BY plan_hash_value;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => '+HINT_REPORT'));

Related Topics

• Performance — broader tuning techniques including stats and parallelism
• Indexes — index types and when each one is used by the optimiser
• Partitioning — partition pruning visible in execution plans
• Joins — join semantics; the plan shows which physical algorithm is chosen
• Data Dictionary — V$SQL, V$SQL_PLAN, and other dictionary views for plan analysis