EXPLAIN PLAN Command in Oracle

The EXPLAIN PLAN command shows how Oracle intends to execute a SQL statement. It records the steps the optimizer chooses—joins, access paths, index usage, and ordering of operations—so you can review the logical execution plan without actually running the query.

You can use EXPLAIN PLAN with SELECT, INSERT, UPDATE, DELETE, and some DDL statements. The resulting execution plan is stored in a plan table and is typically displayed using the DBMS_XPLAN package.

Keep in mind that EXPLAIN PLAN shows a predicted plan based on current statistics, parameters, and metadata. For precise tuning, you should compare this predicted plan with the actual execution plan (for example, using DBMS_XPLAN.DISPLAY_CURSOR after running the statement).

Important: EXPLAIN PLAN is point-in-time and advisory. It tells you which plan the optimizer would choose now, under the current environment:

Initialization and session parameters (e.g., optimizer_mode, optimizer_features_enable).
Object statistics and histograms.
Available indexes, constraints, and materialized views.

The plan that was actually used in a past execution may differ if anything changed: statistics were refreshed, indexes were added or dropped, or the optimizer version evolved. For production troubleshooting, EXPLAIN PLAN is a starting point; real tuning decisions should be based on actual resource usage and runtime plans.

Basic Workflow for Using EXPLAIN PLAN

In modern Oracle releases, a global PLAN_TABLE is typically installed by default. If it is missing, you can create it with the supplied script (for example, utlxplan.sql in the $ORACLE_HOME/rdbms/admin directory).

To capture an execution plan without running the SQL, follow these steps:

Prepare the PLAN_TABLE
Ensure the plan table exists in your schema (or use the shared one created by Oracle).
Issue EXPLAIN PLAN FOR …
Prefix your SQL statement with the EXPLAIN PLAN command:
```
EXPLAIN PLAN
FOR
SELECT /*+ USE_HASH (bookshelf) */
       bsa.authorname
FROM   bookshelf         b
       JOIN bookshelf_author bsa
         ON b.title = bsa.title;
```
This does not execute the query; it only populates the plan table with the predicted plan.
Display the plan with DBMS_XPLAN
Query the plan table using the DBMS_XPLAN package:
```
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY());
```
This returns a formatted plan with operations, row estimates, and relative cost.

Oracle also provides helper scripts (such as utlxplp.sql and utlxpls.sql) that wrap calls to DBMS_XPLAN.DISPLAY with common formatting options.

Sample EXPLAIN PLAN Output

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id  | Operation          | Name             | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |   37 |  1924 |    4  (25) | 00:00:01
|*  1 | HASH JOIN          |                  |   37 |  1924 |    4  (25) | 00:00:01
|   2 | INDEX FULL SCAN    | SYS_C004834      |   32 |   608 |    1 (100) | 00:00:01
|   3 | TABLE ACCESS FULL  | BOOKSHELF_AUTHOR |   37 |  1258 |    4  (25) | 00:00:01
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."TITLE"="BSA"."TITLE")

The key columns in the plan output include:

Id: Step identifier. Parent/child relationships are shown by indentation.
Operation / Name: The operation (e.g., HASH JOIN, TABLE ACCESS FULL, INDEX RANGE SCAN) and the object name (table, index, or view).
Rows: Estimated cardinality (number of rows) for that step.
Cost: A relative, unitless cost value used by the optimizer to compare alternative plans.
Predicate Information: The filter and join predicates applied at each step, helping you see where rows are being filtered.

When tuning queries, you typically look for:

Expensive operations (high estimated cost) early in the plan.
Full table scans on very large tables where a selective index access would be more efficient.
Join methods (nested loops vs. hash join vs. sort-merge) that don’t match data volume or indexing strategy.

Explain Plan vs. Actual Runtime Plan

EXPLAIN PLAN is useful for “what if” analysis—testing hints, new indexes, or rewritten SQL without executing the statement. However, it does not show actual runtime statistics (such as real row counts, I/O, or CPU).

In current Oracle releases (e.g., 19c and 23c/23ai), best practice is:

Use EXPLAIN PLAN and DBMS_XPLAN.DISPLAY to understand the optimizer’s chosen strategy.
Use DBMS_XPLAN.DISPLAY_CURSOR to display the actual plan and row source statistics for executed statements.
Supplement plan analysis with AWR/ASH, SQL Monitoring, or SQL Trace/TKPROF when deeper profiling is needed.

Taken together, these tools let you verify whether the plan is appropriate and where the real performance bottlenecks occur.