How to EXPLAIN PLAN in Oracle

How do I use EXPLAIN PLAN to view an Oracle execution plan?

EXPLAIN PLAN shows the execution steps the Oracle optimizer will take for a SQL statement without running the statement.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does EXPLAIN PLAN do in Oracle?

EXPLAIN PLAN inserts the optimizer’s chosen execution steps into PLAN_TABLE so you can inspect access paths, join methods, and costs before running a query.

How do I generate an execution plan?

Run EXPLAIN PLAN FOR followed by the SQL, then view it with SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());.The query itself never executes, keeping data safe.

Quick example with Orders

EXPLAIN PLAN FORSELECT *FROM OrdersWHERE order_date > SYSDATE - 7;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

How can I store and compare multiple plans?

Add SET STATEMENT_ID = 'v1' to tag a plan, rerun with a new ID after adding indexes, then compare both IDs in PLAN_TABLE.

When should I check an execution plan?

Investigate slow reports, validate new releases, or verify that recent indexes are used.Early checks prevent full table scans in production.

Best practices for reading plans?

Start at the deepest node to inspect access paths, move upward through join operations, and flag steps with high cost or large estimated rows.

Why How to EXPLAIN PLAN in Oracle is important

How to EXPLAIN PLAN in Oracle Example Usage


-- Tag the plan
EXPLAIN PLAN SET STATEMENT_ID='cust_orders' FOR
SELECT c.name,
       SUM(oi.quantity * p.price) AS total_spent
FROM   Customers   c
JOIN   Orders      o  ON o.customer_id = c.id
JOIN   OrderItems  oi ON oi.order_id   = o.id
JOIN   Products    p  ON p.id          = oi.product_id
WHERE  c.id = 42
GROUP  BY c.name;

-- View the plan
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY(NULL, 'cust_orders', 'ALL'));

How to EXPLAIN PLAN in Oracle Syntax


EXPLAIN PLAN [SET STATEMENT_ID = 'id'] [INTO plan_table] FOR
<SQL_statement>;

-- Display the stored plan
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY('[plan_table]', 'id', 'TYPICAL'));

Common Mistakes

Running EXPLAIN PLAN and assuming the output appears automatically. Fix: always call <code>DBMS_XPLAN.DISPLAY</code> (or query <code>PLAN_TABLE</code>) to see the stored plan.
Comparing plans from sessions with different optimizer parameters. Fix: ensure both sessions share identical <code>OPTIMIZER_MODE</code>, statistics, and bind values before comparing.