Oracle SQL Execution Plan Analysis: A Practical Guide to EXPLAIN PLAN, AUTOTRACE, and DBMS_XPLAN

When a complex SQL query begins running slowly in Oracle Database, developers often guess at solutions: adding random indexes, changing table order, or tossing in hints. Professional performance tuning, however, requires reading the Execution Plan—the exact set of operational steps generated by Oracle’s Cost-Based Optimizer (CBO) to retrieve the requested dataset.

In this guide, we break down how to generate execution plans accurately, how to read the execution hierarchy correctly, and how to spot the most common performance bottlenecks using EXPLAIN PLAN, SQL*Plus AUTOTRACE, and DBMS_XPLAN.DISPLAY_CURSOR.

1. Generating Execution Plans: Three Essential Methods

Oracle provides multiple ways to view query plans, but they are not all equal in precision:

Method A: EXPLAIN PLAN (Estimated Plan)

EXPLAIN PLAN generates an estimate of how Oracle would run the query without actually executing it:

EXPLAIN PLAN FOR
SELECT o.order_id, c.customer_name, o.order_total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= DATE '2026-01-01'
  AND c.status = 'ACTIVE';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

While fast and safe for UPDATE or DELETE statements, estimated plans can diverge from runtime plans due to bind variable peeking, dynamic sampling, or cursor sharing.

Method B: AUTOTRACE (Execution + Execution Statistics)

In SQL Developer or SQL*Plus, SET AUTOTRACE ON executes the query, displays the result, and outputs both the plan and the physical I/O statistics (consistent gets, physical reads, redo size):

SET AUTOTRACE TRACEONLY;
SELECT * FROM sales WHERE sale_year = 2026;
SET AUTOTRACE OFF;

Method C: DBMS_XPLAN.DISPLAY_CURSOR (The Gold Standard)

To inspect the actual execution plan from memory along with real row counts and runtime durations, query the cursor cache using DBMS_XPLAN.DISPLAY_CURSOR with the ALLSTATS LAST format:

-- Step 1: Run query with GATHER_PLAN_STATISTICS hint
SELECT /*+ GATHER_PLAN_STATISTICS */ o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_id = 905412;

-- Step 2: Display the actual runtime statistics of the last executed statement
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

2. How to Read Execution Plan Hierarchy Correctly

The most common mistake when reviewing an execution plan is reading it from top to bottom like a book. Execution plans are hierarchical trees. The rule of evaluation is:

Hierarchy Rule: “Start with the most deeply indented child operation. If two operations have the exact same indentation, execute the top one first.”

Consider this standard execution plan output:

--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |    12 |   480 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |                  |    12 |   480 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID | ORDERS           |     3 |    60 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN           | IDX_ORDERS_DT    |     3 |       |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID | CUSTOMERS        |     4 |   100 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | PK_CUSTOMERS     |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("O"."ORDER_DATE">=TO_DATE(' 2026-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   5 - access("O"."CUSTOMER_ID"="C"."CUSTOMER_ID")

The sequence of operations is:

1. Operation 3: INDEX RANGE SCAN on IDX_ORDERS_DT locates ROWIDs matching the date predicate.
2. Operation 2: TABLE ACCESS BY INDEX ROWID fetches the matching rows from the ORDERS table.
3. Operation 1: NESTED LOOPS joins these orders with customers.
4. Operation 5: For each order, INDEX UNIQUE SCAN looks up the customer in PK_CUSTOMERS.
5. Operation 4: TABLE ACCESS BY INDEX ROWID retrieves the customer row.
6. Operation 0: Results are returned to the client.

3. Key Join Operations Explained

Oracle utilizes three primary join mechanisms. Identifying which join the optimizer selected reveals whether it properly understood the dataset size:

Join Method	How It Works	Optimal When	Red Flag When
Nested Loops	For each row in the driving table, probe the inner table (usually via index).	Driving table is small (few rows), inner table has indexed lookup.	Driving table returns millions of rows (causing millions of single-block reads).
Hash Join	Builds an in-memory hash table of the smaller table, then streams and probes the larger table.	Joining moderate to large datasets with equality operators (=).	Memory is limited (spilling hash partitions to TEMP tablespace).
Sort Merge Join	Sorts both datasets by the join key, then walks both streams in parallel.	Non-equality joins (<, >=, BETWEEN) or inputs already sorted.	Unnecessary sorting overhead on large unsorted tables.

4. Access Predicates vs. Filter Predicates

At the bottom of every execution plan, review the Predicate Information section. Distinguishing between access and filter predicates is vital:

• Access Predicate: Directly navigates the index tree structure. Only relevant blocks are visited. This is fast and efficient.
• Filter Predicate: Rows or index keys are evaluated after they have already been retrieved into memory. If an index scan has only filter predicates and no access predicates, Oracle is scanning the index blindly from start to finish.

5. Diagnosing Cardinality Misestimates with ALLSTATS LAST

The single most frequent root cause of a terrible execution plan is a cardinality misestimate: the CBO expects 5 rows, but the query actually retrieves 500,000 rows. Because the optimizer expected 5 rows, it selected a Nested Loops join; in reality, executing a loop half a million times destroys performance.

Using ALLSTATS LAST displays two columns side by side: E-Rows (Estimated Rows) and A-Rows (Actual Rows):

-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        | 450000 |00:00:18.42 |  128500 |  42100 |
|   1 |  NESTED LOOPS      |        |      1 |      4 | 450000 |00:00:18.42 |  128500 |  42100 |
|*  2 |   TABLE ACCESS FULL| ORDERS |      1 |      4 | 450000 |00:00:01.12 |    8400 |      0 |
-----------------------------------------------------------------------------------------------

Notice the huge discrepancy at Operation 2: E-Rows was 4, but A-Rows was 450,000! The solution here is not hinting; it is updating stale table statistics or creating extended column group statistics with DBMS_STATS so the optimizer knows the true distribution of the data.

Conclusion

Never guess when tuning an Oracle SQL query. Always pull the actual execution plan with DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'), compare estimated versus actual rows, inspect the driving table and join mechanisms, and address the root statistical or indexing cause.