Execution Plans: EXPLAIN PLAN and DBMS_XPLAN

Oracle DBA Certification 15 min min read Updated: Nov 16, 2026 Intermediate
Execution Plans: EXPLAIN PLAN and DBMS_XPLAN
Intermediate Topic 2 of 7

What is an Execution Plan?

An execution plan is the step-by-step route Oracle chooses to run your SQL: which indexes, which join method, in which order. Reading plans is the central skill of SQL tuning.

Method 1: EXPLAIN PLAN (Estimated)

sql EXPLAIN PLAN FOR SELECT c.full_name, o.status FROM shop.orders o JOIN shop.customers c ON c.customer_id = o.customer_id WHERE o.status = 'NEW'; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Limit: EXPLAIN PLAN shows what Oracle would do. It does not run the query, so it can differ from what happens in real life.

Method 2: The Real Plan with Actual Rows

sql SELECT /*+ GATHER_PLAN_STATISTICS */ c.full_name, o.status FROM shop.orders o JOIN shop.customers c ON c.customer_id = o.customer_id WHERE o.status = 'NEW'; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

This shows E-Rows (estimated) and A-Rows (actual). When they are very different, the statistics are probably wrong.

Common Plan Operations

OperationMeaningGood or bad?
TABLE ACCESS FULLReads the whole tableFine for small tables or when most rows are needed
INDEX UNIQUE SCANFinds one row by a unique keyExcellent
INDEX RANGE SCANReads a range of index entriesGood
TABLE ACCESS BY INDEX ROWIDFetches the row after the indexNormal
NESTED LOOPSFor each row on the left, look up the rightGood for few rows
HASH JOINBuilds a hash table in memory and probes itGood for large sets
SORT ORDER BY, HASH GROUP BYSorting and groupingWatch memory and TEMP

How to Read a Plan

  1. Look at the indentation. The most indented line runs first.
  2. At the same level, the first child runs first.
  3. Check Rows or A-Rows: where do millions of rows appear?
  4. Look for TABLE ACCESS FULL on a big table with a very selective WHERE.
  5. Compare E-Rows with A-Rows to detect bad statistics.

Predicate Information

At the bottom of the plan you see access and filter predicates. access means the condition was used to find rows efficiently (through an index). filter means rows were read first and then thrown away.

A Plan Before and After an Index

sql CREATE INDEX shop.idx_orders_status ON shop.orders (status); EXEC DBMS_STATS.GATHER_TABLE_STATS('SHOP','ORDERS'); EXPLAIN PLAN FOR SELECT * FROM shop.orders WHERE status = 'CANCELLED'; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Remember: Oracle may still choose a full scan after you add an index. If the query returns a large percent of the table, a full scan really is faster. Trust the optimizer, but check the statistics.

Find the Plan of a Slow Query by SQL_ID

sql SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'TYPICAL'));

Conclusion

An execution plan shows how Oracle runs your SQL. Look for big row counts, unwanted full scans and gaps between estimated and actual rows.

Quick Summary: EXPLAIN PLAN = estimate, DISPLAY_CURSOR = real run. Compare E-Rows to A-Rows.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators