An execution plan is the list of steps Oracle uses to run a SQL statement: which tables it reads and how, in which order it joins them, and how many rows it expects at each step. Reading a plan is the first step in tuning any slow query, because it shows where the work goes. This guide gets plans with EXPLAIN PLAN and DBMS_XPLAN, reads every part of them, from the operation tree and its order to the cost columns, the predicates, and the notes, and finally compares the optimizer's estimates with the rows a real execution produced.
Syntax
explain plan for select ...; select * from dbms_xplan.display();
The statement after EXPLAIN PLAN FOR can be any SELECT, INSERT, UPDATE, DELETE, or MERGE. EXPLAIN PLAN does not run the statement. It stores the plan the optimizer would choose in PLAN_TABLE, and DBMS_XPLAN.DISPLAY formats it.
Sample Tables
The examples use 5,000 customers and 100,000 orders, with statistics gathered:
Example:
create table demo_customers (
customer_id number constraint demo_customers_pk primary key,
name varchar2(40) not null,
country varchar2(20) not null,
city varchar2(20) not null);
create table demo_orders (
order_id number constraint demo_orders_pk primary key,
customer_id number not null references demo_customers,
order_date date not null,
amount number(10,2) not null);
insert into demo_customers
select level, 'Customer ' || level,
decode(mod(level, 4), 0, 'India', 1, 'Germany', 2, 'Brazil', 'Japan'),
decode(mod(level, 4), 0, decode(mod(level, 3), 0, 'Delhi', 1, 'Mumbai', 'Pune'),
1, 'Berlin', 2, 'Recife', 'Osaka')
from dual connect by level <= 5000;
insert into demo_orders
select level, mod(level * 7, 5000) + 1,
date '2025-01-01' + mod(level, 600), mod(level * 13, 500) + 10
from dual connect by level <= 100000;
commit;
create index demo_orders_customer_ix on demo_orders (customer_id);
exec dbms_stats.gather_table_stats(user, 'DEMO_CUSTOMERS')
exec dbms_stats.gather_table_stats(user, 'DEMO_ORDERS')Output:
Table DEMO_CUSTOMERS created. Table DEMO_ORDERS created. 5,000 rows inserted. 100,000 rows inserted. Commit complete. Index DEMO_ORDERS_CUSTOMER_IX created. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.
Get a Plan with EXPLAIN PLAN
Start with a simple lookup by primary key:
Example:
explain plan for select * from demo_orders where order_id = 42; select * from dbms_xplan.display();
Output:
Explained.
PLAN_TABLE_OUTPUT
_________________________________________________________________________________________________
Plan hash value: 969379040
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEMO_ORDERS | 1 | 21 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | DEMO_ORDERS_PK | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORDER_ID"=42)
14 rows selected.Read the Operation Tree
Each line is one operation, numbered by Id. The indentation of the Operation column shows the tree: an operation indented further to the right is a child, and it passes its rows up to the nearest operation above it with less indentation, its parent.
- Id 2, INDEX UNIQUE SCAN of DEMO_ORDERS_PK, finds the index entry for order 42 and returns its ROWID, the row's address.
- Id 1, TABLE ACCESS BY INDEX ROWID, reads that one row of DEMO_ORDERS.
- Id 0, SELECT STATEMENT, returns the row to the caller.
So the plan runs from the inside out: the deepest operation works first, even though it is printed last.
A Plan with a Join
A query that filters, joins, groups, and sorts has more steps:
Example:
explain plan for select c.city, count(*) as orders, sum(o.amount) as total from demo_orders o join demo_customers c on c.customer_id = o.customer_id where c.country = 'India' and o.order_date >= date '2026-01-01' group by c.city order by total desc; select * from dbms_xplan.display();
Output:
Explained.
PLAN_TABLE_OUTPUT
__________________________________________________________________________________________
Plan hash value: 274767803
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 204 | 114 (3)| 00:00:01 |
| 1 | SORT ORDER BY | | 6 | 204 | 114 (3)| 00:00:01 |
| 2 | HASH GROUP BY | | 6 | 204 | 114 (3)| 00:00:01 |
|* 3 | HASH JOIN | | 9808 | 325K| 112 (1)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| DEMO_CUSTOMERS | 1250 | 22500 | 9 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| DEMO_ORDERS | 39232 | 613K| 102 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C"."CUSTOMER_ID"="O"."CUSTOMER_ID")
4 - filter("C"."COUNTRY"='India')
5 - filter("O"."ORDER_DATE">=TO_DATE(' 2026-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Note
-----
- this is an adaptive plan
24 rows selected.Two rules give the order: children run before their parent, and of two children, the upper one runs first. The plan therefore runs in this order:
- Id 4 reads all of DEMO_CUSTOMERS and keeps the customers in India.
- Id 3, the HASH JOIN, builds a hash table in memory from those customers. The first child of a hash join is this build input, and the optimizer puts the smaller row source there.
- Id 5 reads all of DEMO_ORDERS and keeps the orders from 2026 on. Each order is looked up in the hash table, and matching pairs leave the join.
- Id 2 groups the joined rows by city, Id 1 sorts the groups by total, and Id 0 returns them.
What the Columns Mean
- Rows: the number of rows the optimizer expects the operation to return, for example 1,250 customers in India and 39,232 orders from 2026 on.
- Bytes: the expected amount of data those rows hold. K means kilobytes.
- Cost (%CPU): the optimizer's estimate of the work, in its own units, with the share of CPU in brackets. Cost is cumulative: the hash join's 112 includes its children, 9 and 102, and the top line's 114 is the cost of the whole statement.
- Time: the expected elapsed time, a rough figure derived from the cost.
All of these are estimates, based on the table and column statistics. Cost is useful for comparing the steps of one plan, not for comparing different statements.
Predicate Information
An asterisk before an Id means the operation has a predicate, listed under Predicate Information:
- access means the condition is used to find rows: the index lookup on ORDER_ID, or the join key of the hash join.
- filter means rows are read first and the condition then discards those that do not match, as in the full scans on COUNTRY and ORDER_DATE.
A filter on a large full scan is often the place where an index could help. The date literal appears as TO_DATE because Oracle shows the predicate as it evaluates it.
The Note Section
"this is an adaptive plan" means the optimizer prepared an alternative for part of the plan, such as another join method, and decides between them while the statement runs, after it has seen how many rows really arrive. The plan shown is the default one. Other notes report, for example, that dynamic sampling was used because statistics were missing.
Compare the Estimates with the Actual Rows
Running the statement with the GATHER_PLAN_STATISTICS hint records the real row counts of every step, and DBMS_XPLAN.DISPLAY_CURSOR shows them next to the estimates:
Example:
set serveroutput off select /*+ gather_plan_statistics */ c.city, count(*) as orders, sum(o.amount) as total from demo_orders o join demo_customers c on c.customer_id = o.customer_id where c.country = 'India' and o.order_date >= date '2026-01-01' group by c.city order by total desc; select * from dbms_xplan.display_cursor(format => 'IOSTATS LAST');
Output:
CITY ORDERS TOTAL
_________ _________ _________
Delhi 3268 852384
Mumbai 3271 839081
Pune 3264 836724
PLAN_TABLE_OUTPUT
____________________________________________________________________________________________________
SQL_ID ab6cr4vt94pbr, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ c.city, count(*) as orders,
sum(o.amount) as total from demo_orders o join demo_customers c on
c.customer_id = o.customer_id where c.country = 'India' and
o.order_date >= date '2026-01-01' group by c.city order by total desc
Plan hash value: 274767803
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 403 |
| 1 | SORT ORDER BY | | 1 | 6 | 3 |00:00:00.01 | 403 |
| 2 | HASH GROUP BY | | 1 | 6 | 3 |00:00:00.01 | 403 |
|* 3 | HASH JOIN | | 1 | 9808 | 9803 |00:00:00.01 | 403 |
|* 4 | TABLE ACCESS FULL| DEMO_CUSTOMERS | 1 | 1250 | 1250 |00:00:00.01 | 30 |
|* 5 | TABLE ACCESS FULL| DEMO_ORDERS | 1 | 39232 | 39046 |00:00:00.01 | 373 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C"."CUSTOMER_ID"="O"."CUSTOMER_ID")
4 - filter("C"."COUNTRY"='India')
5 - filter("O"."ORDER_DATE">=TO_DATE(' 2026-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Note
-----
- this is an adaptive plan
32 rows selected.- Starts is how often the operation ran, E-Rows the estimated rows, A-Rows the actual rows, A-Time the actual time, and Buffers the blocks read from memory.
- The estimates are close: 9,808 joined rows expected, 9,803 found. Statistics are good, and the plan fits the data.
- The grouping expected 6 cities and found 3: the statistics know six cities in the table, but not which of them belong to India. A small error in a small step does no harm.
- The Plan hash value, 274767803, is the same as in the EXPLAIN PLAN output, so the statement really ran with the plan that was explained.
- SET SERVEROUTPUT OFF matters in SQL*Plus and SQLcl: with it on, the tool runs its own DBMS_OUTPUT call after each statement, and DISPLAY_CURSOR would show that call's plan instead.
When E-Rows and A-Rows differ by a factor of ten or more in a large step, the optimizer is working from wrong numbers, and that is usually where a bad plan comes from.
Things to Know
- EXPLAIN PLAN shows the plan the optimizer would choose now. The plan actually used can differ, for example with bind variables; DISPLAY_CURSOR shows the real one.
- Rows in the plan are per execution of the operation. When Starts is greater than 1, as for the inner side of a nested loop, multiply to get the total.
- Read a plan bottom-up within each branch and look for three things: the steps with the highest cost, full scans with filters on large tables, and big gaps between estimated and actual rows.
Related Guides
Conclusion
An Oracle execution plan is a tree of operations that runs from the inside out: children before parents, upper children first. Read the estimated rows and cost of each step, check whether predicates are access or filter conditions, and run the statement with GATHER_PLAN_STATISTICS to compare the estimates with what really happened.
