How to Read Explain Plan in Oracle (With Examples)

To read an explain plan in Oracle, read it as a tree, not as a list. Find the operations that are indented furthest to the right. Those run first and fetch the data. Their rows flow up to the parent operation, one level to the left, until they reach line 0, the SELECT STATEMENT.

That one rule explains most plans. In this tutorial you will generate a plan with EXPLAIN PLAN FOR and DBMS_XPLAN.DISPLAY, read every column, understand the Predicate Information section, and then compare the optimizer's guesses with real row counts using DBMS_XPLAN.DISPLAY_CURSOR. The examples follow the Oracle 19c documentation and also apply to 21c and 23ai.

What an explain plan is

When you send a query, the optimizer decides how to run it. It picks an access path for each table (full scan, index scan), a join order and a join method. That set of steps is the execution plan.

EXPLAIN PLAN asks the optimizer to describe the plan it would use, without running the query. It writes the steps into a table called PLAN_TABLE. DBMS_XPLAN.DISPLAY then formats those rows into the familiar text grid.

This difference matters. An explain plan is a prediction. The plan the database actually used is stored with the cursor in the shared pool, and you read that with DBMS_XPLAN.DISPLAY_CURSOR. The Oracle docs warn that the two can differ, for example when the query uses bind variables, when a different user runs it, or when session parameters differ.

Sample setup

Create two small tables so you can follow along. Then gather statistics, because the optimizer relies on them to estimate row counts.

CREATE TABLE customers (
  customer_id   NUMBER PRIMARY KEY,
  customer_name VARCHAR2(50) NOT NULL,
  city          VARCHAR2(30) NOT NULL
);

CREATE TABLE orders (
  order_id     NUMBER PRIMARY KEY,
  customer_id  NUMBER NOT NULL REFERENCES customers,
  order_date   DATE NOT NULL,
  amount       NUMBER(10,2) NOT NULL,
  status       VARCHAR2(10) NOT NULL
);

CREATE INDEX orders_cust_ix ON orders (customer_id);

INSERT INTO customers VALUES (1, 'Asha Rao',     'Bengaluru');
INSERT INTO customers VALUES (2, 'Vikram Singh', 'Delhi');
INSERT INTO customers VALUES (3, 'Meera Iyer',   'Chennai');
INSERT INTO customers VALUES (4, 'Rahul Shah',   'Mumbai');

INSERT INTO orders VALUES (101, 1, DATE '2026-09-01', 2500,  'SHIPPED');
INSERT INTO orders VALUES (102, 2, DATE '2026-09-03', 1200,  'PENDING');
INSERT INTO orders VALUES (103, 1, DATE '2026-09-05', 800,   'SHIPPED');
INSERT INTO orders VALUES (104, 3, DATE '2026-09-07', 4300,  'CANCELLED');
INSERT INTO orders VALUES (105, 4, DATE '2026-09-10', 1500,  'PENDING');
INSERT INTO orders VALUES (106, 2, DATE '2026-09-12', 3100,  'SHIPPED');
COMMIT;

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'CUSTOMERS');
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS');
END;
/

Step 1: generate and display a plan

Explain a simple query

EXPLAIN PLAN FOR
  SELECT order_id, amount
    FROM orders
   WHERE order_id = 103;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

EXPLAIN PLAN does not run the query, so it returns instantly even for a query that would take an hour. The second statement reads the most recent plan from PLAN_TABLE.

-------------------------------------------------------------------------------
| Id | Operation                   | Name         | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |              |    1 |     8 |     1   (0)|
|  1 |  TABLE ACCESS BY INDEX ROWID| ORDERS       |    1 |     8 |     1   (0)|
|* 2 |   INDEX UNIQUE SCAN         | SYS_C0012345 |    1 |       |     0   (0)|
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
-------------------------------------------------------------------------------

   2 - access("ORDER_ID"=103)

Read it from the most indented line. Step 2 looks up the value 103 in the primary key index and gets back a rowid. Step 1 uses that rowid to fetch the row from the ORDERS table. Step 0 returns the row to you. The index name is system generated, so yours will have a different SYS_C number.

The real output also starts with a line like Plan hash value: .... That number identifies the plan shape. If the hash value changes between two runs, the plan changed.

What each column means

Step 2: read the execution order of a join

Single-table plans are easy. Joins are where people get lost, because the Id numbers are not the order of execution. Here is a join with a filter and a sort.

EXPLAIN PLAN FOR
  SELECT c.customer_name, o.order_id, o.amount
    FROM customers c
    JOIN orders o ON o.customer_id = c.customer_id
   WHERE c.city = 'Delhi'
   ORDER BY o.amount DESC;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

--------------------------------------------------------------------
| Id | Operation           | Name      | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
|  0 | SELECT STATEMENT    |           |    2 |    60 |     7  (15)|
|  1 |  SORT ORDER BY      |           |    2 |    60 |     7  (15)|
|* 2 |   HASH JOIN         |           |    2 |    60 |     6   (0)|
|* 3 |    TABLE ACCESS FULL| CUSTOMERS |    1 |    20 |     3   (0)|
|  4 |    TABLE ACCESS FULL| ORDERS    |    6 |    60 |     3   (0)|
--------------------------------------------------------------------

Predicate Information (identified by operation id):
--------------------------------------------------------------------

   2 - access("O"."CUSTOMER_ID"="C"."CUSTOMER_ID")
   3 - filter("C"."CITY"='Delhi')

With tables this small, Oracle may choose NESTED LOOPS with the ORDERS_CUST_IX index instead of a hash join. Both plans are read the same way.

Walk the tree from the leaves up

1. Step 3 runs first. It is a leaf (it has no children) and it is the first child of the hash join. It scans CUSTOMERS and keeps only rows where city is Delhi. The optimizer expects 1 row.
2. The hash join (step 2) builds an in-memory hash table on CUSTOMER_ID from those rows. The first child of a hash join is the build input.
3. Step 4 scans ORDERS. Each order row probes the hash table. This second child is the probe input.
4. Step 2 outputs the matching rows: Delhi customers with their orders.
5. Step 1 sorts the joined rows by amount, descending.
6. Step 0 returns the sorted rows to the client.

So the order of work is 3, 4, 2, 1, 0. Here are the rules that got us there:

• A step's children are the lines directly below it with one more level of indentation.
• A child must produce rows before its parent can use them, so start at the leaves.
• When two children share a parent, the upper one (the first child) is processed first.
• Operations that need all their input before returning anything, such as SORT ORDER BY or the build side of HASH JOIN, finish their input completely. Others, such as NESTED LOOPS, pass rows up one at a time.

The Oracle 19c SQL Tuning Guide walks through its own example the same way: the access paths at the bottom of the tree retrieve the data, and the join steps above them accept those row sources and return the result.

Step 3: understand access and filter predicates

The Predicate Information section is the most useful part of the plan, and many people skip it. It tells you how each condition in your WHERE clause is applied.

In the join example, the city condition is a filter on a full table scan. Oracle reads every customer row and discards the ones not in Delhi. On four rows that is fine. On four million rows it may not be.

Look at this query on the status column, which has no index:

EXPLAIN PLAN FOR
  SELECT order_id, amount
    FROM orders
   WHERE status = 'PENDING';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

---------------------------------------------------------------
| Id | Operation         | Name   | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|  0 | SELECT STATEMENT  |        |    2 |    32 |     3   (0)|
|* 1 |  TABLE ACCESS FULL| ORDERS |    2 |    32 |     3   (0)|
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------------------

   1 - filter("STATUS"='PENDING')

A filter on TABLE ACCESS FULL means every row was read to find two. If this table were large and the condition very selective, an index on status could turn the filter into an access predicate. If most rows are PENDING, the full scan is the right choice, and an index would not help.

Step 4: see the actual plan with real row counts

An explain plan only shows estimates. To find out where the optimizer guessed wrong, run the query and compare estimated rows with actual rows. Oracle collects these runtime statistics when you add the GATHER_PLAN_STATISTICS hint or set STATISTICS_LEVEL to ALL.

Run the query with the hint

SELECT /*+ GATHER_PLAN_STATISTICS */
       c.customer_name, o.order_id, o.amount
  FROM customers c
  JOIN orders o ON o.customer_id = c.customer_id
 WHERE c.city = 'Delhi'
 ORDER BY o.amount DESC;

Display the plan of the last statement

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

With no sql_id, DISPLAY_CURSOR shows the last cursor executed by your session. ALLSTATS adds I/O and memory statistics, and LAST limits them to the most recent execution instead of the total of all executions.

-----------------------------------------------------------------------------
| Id | Operation           | Name      | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |           |      1 |        |      2 |      14 |
|  1 |  SORT ORDER BY      |           |      1 |      2 |      2 |      14 |
|* 2 |   HASH JOIN         |           |      1 |      2 |      2 |      14 |
|* 3 |    TABLE ACCESS FULL| CUSTOMERS |      1 |      1 |      1 |       7 |
|  4 |    TABLE ACCESS FULL| ORDERS    |      1 |      6 |      6 |       7 |
-----------------------------------------------------------------------------

Here the estimates match reality, which is what you want. When tuning a slow query, scan down for the first step where E-Rows multiplied by Starts is far from A-Rows, say 10 estimated against 200,000 actual. That bad estimate usually explains a poor join method or join order above it. Common causes are stale statistics, correlated columns and skewed data.

To look at a statement that ran in another session, find its SQL_ID in V$SQL and pass it in: DBMS_XPLAN.DISPLAY_CURSOR('your_sql_id', NULL, 'ALLSTATS LAST'). Passing NULL as the child number shows all child cursors. You need SELECT access on V$SQL, V$SQL_PLAN and V$SQL_PLAN_STATISTICS_ALL, which the SELECT_CATALOG_ROLE role includes.

Common mistakes when reading explain plans

• Reading the plan top to bottom. Step 1 is not the first thing that happens. Start at the most indented leaf and work up.
• Comparing cost across different queries. Cost only ranks alternative plans for the same statement. Use elapsed time and Buffers from DISPLAY_CURSOR to compare two rewrites of a query.
• Treating every full table scan as a problem. Reading a whole small table, or most of a big one, is often cheaper than thousands of index lookups. Judge it by the rows returned compared with rows in the table.
• Trusting EXPLAIN PLAN for queries with bind variables. EXPLAIN PLAN does not peek at bind values, so the real plan can differ. Check the cursor plan with DISPLAY_CURSOR.
• Getting the wrong statement from DISPLAY_CURSOR(). In SQL*Plus or SQL Developer with SERVEROUTPUT ON, the tool runs an extra call after your query to fetch DBMS_OUTPUT, so the "last cursor" is not your query. Run SET SERVEROUTPUT OFF first, or pass the SQL_ID.
• Seeing E-Rows but no A-Rows. You asked for ALLSTATS without the GATHER_PLAN_STATISTICS hint or STATISTICS_LEVEL = ALL, so no runtime statistics were collected.
• Mixing plans in PLAN_TABLE. If you explain several statements, tag each with SET STATEMENT_ID = 'name' and pass that ID to DBMS_XPLAN.DISPLAY. Oracle advises deleting existing rows for a statement ID before reusing it.

Tips for faster plan reading

• Gather statistics after large data loads. Most bad plans start with bad estimates.
• Use format => 'ALL' in DBMS_XPLAN.DISPLAY when you need the column projection and query block names. Use 'BASIC' when you only want the shape.
• Read the Note section at the bottom of the output. It tells you when dynamic sampling, an adaptive plan or a SQL plan baseline was used.
• In a big plan, look first at the steps with the highest A-Rows or Buffers. That is where the time goes.
• Fix the earliest bad estimate in the tree first. Errors below it multiply as rows flow upward.

Frequently asked questions

How do I read an explain plan in Oracle SQL Developer?

Press F10 (or click the Explain Plan button) with the query in the worksheet. SQL Developer shows the same operations as a tree you can expand. The reading rules are the same: start at the deepest node and move up to its parent. Running the DBMS_XPLAN queries in the worksheet gives you the text version shown in this article.

What is the difference between explain plan and execution plan in Oracle?

An explain plan is the optimizer's prediction, produced without running the query. An execution plan is what the database actually used when the statement ran, stored with the cursor. Use DISPLAY_CURSOR to see the actual plan and, with plan statistics, actual row counts.

What is cardinality vs cost in an Oracle explain plan?

Cardinality (the Rows or E-Rows column) is the estimated number of rows a step returns. Cost is the estimated amount of work for the step and everything below it. Cost is calculated from cardinality, so a wrong cardinality leads to a wrong cost and often a poor plan.

How do I get the explain plan for a SQL_ID?

If the cursor is still in the shared pool, run SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('your_sql_id')). If it has aged out and you are licensed for the Diagnostics Pack, DBMS_XPLAN.DISPLAY_WORKLOAD_REPOSITORY (19c and later) or the older DISPLAY_AWR can show plans stored in AWR.

Does EXPLAIN PLAN execute the query?

No. It only parses the statement and writes the predicted plan into PLAN_TABLE. It is safe to use on a DELETE or UPDATE. Note that EXPLAIN PLAN itself is a DML statement on PLAN_TABLE, and it does not commit.

Summary

• Generate a predicted plan with EXPLAIN PLAN FOR and show it with DBMS_XPLAN.DISPLAY.
• Read the plan as a tree: the most indented leaf runs first, and the first child of a parent before the second.
• Rows is an estimate and Cost is only for comparing plans of the same query.
• Check Predicate Information: access predicates locate rows, filter predicates discard rows after reading them.
• Use GATHER_PLAN_STATISTICS with DISPLAY_CURSOR('ALLSTATS LAST') to compare E-Rows with A-Rows.
• Fix the lowest step where estimates and actual rows differ most, often by refreshing statistics.