Oracle EXPLAIN PLAN: SQL Performance Analysis Guide

calendar Aug 6, 2025 / · 8 min read · Explain Plan Query Tuning SQL Performance dbms_xplan  ·
Share on: linkedin copy

Oracle EXPLAIN PLAN: SQL Performance Analysis Guide

Purpose

A full table scan in an execution plan is not always a fault. The optimizer can choose it on purpose. If its statistics show that most rows match, reading the whole table can be the cheapest path. The plan is where you see this choice. EXPLAIN PLAN is the statement that shows it to you.

EXPLAIN PLAN lets you examine the execution plan that the optimizer chose for a SQL statement. The output shows how the database would have run the statement at the time it was explained. It writes one row for each step of the plan into a plan table. You then read that table to see access methods, join methods, cost and cardinality.

A DBA runs this check before a new query goes to production. A DBA also runs it when a query is slow, after an index change, and when two versions of a query need a side-by-side comparison. The steps below show how to produce a plan, how to display it, and how to read what you see.

Code

 1-- 1. Explain a statement (rows go to PLAN_TABLE in your schema)
 2EXPLAIN PLAN FOR
 3SELECT column1, column2
 4FROM table1
 5WHERE condition = 'value';
 6
 7-- 2. Tag a plan with a statement ID so you can find it later
 8EXPLAIN PLAN SET STATEMENT_ID = 'bad1' FOR
 9SELECT column1, column2
10FROM table1
11WHERE condition = 'value';
12
13-- 3. Display the plan with the formatted script
14SET LINES 100 PAGES 999
15@?/rdbms/admin/utlxpls
16
17-- 4. Or display it with DBMS_XPLAN
18SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
19
20-- 5. Compare two versions of one query with two statement IDs
21EXPLAIN PLAN SET STATEMENT_ID = 'optimized_query' FOR
22SELECT /*+ INDEX(emp emp_idx) */ emp_name, salary
23FROM employees emp
24WHERE department_id = 10;
25
26EXPLAIN PLAN SET STATEMENT_ID = 'unoptimized_query' FOR
27SELECT emp_name, salary
28FROM employees
29WHERE department_id = 10;
30
31-- 6. A larger statement to analyze
32EXPLAIN PLAN SET STATEMENT_ID = 'slow_query_analysis' FOR
33SELECT o.order_id, c.customer_name, SUM(oi.quantity * oi.price)
34FROM orders o
35JOIN customers c ON o.customer_id = c.customer_id
36JOIN order_items oi ON o.order_id = oi.order_id
37WHERE o.order_date >= DATE '2025-01-01'
38GROUP BY o.order_id, c.customer_name;

Code Breakdown

Generating the plan

  • EXPLAIN PLAN FOR <statement> asks the optimizer for its plan. If you leave out the INTO clause, the database uses a table named PLAN_TABLE in your own schema. The statement you explain can be a SELECT, INSERT, UPDATE, DELETE or MERGE. It can also be a CREATE TABLE, CREATE INDEX or ALTER INDEX ... REBUILD statement.
  • SET STATEMENT_ID = 'name' puts your own label on the rows of the plan. If you omit it, the value is null. Use a label whenever the plan table holds rows from many plans. Remove old rows with the same label first, or the new plan will mix with them.

Displaying the plan

  • @?/rdbms/admin/utlxpls runs the Oracle script that displays the plan table output for serial processing. A second script, utlxplp.sql, shows the same output with the parallel execution columns.
  • SET LINES 100 PAGES 999 sets a 100-character line and a 999-line page, so the plan prints with few repeated headings.
  • DBMS_XPLAN.DISPLAY is a table function that displays the contents of the plan table. You can pass a plan table name, a statement ID and a format such as TYPICAL.

Comparing two plans

Blocks 5 and 6 show the main reason for statement IDs. You explain two versions of one query under two labels. Then you display each one and compare the steps and the cost. The hint in block 5 only works if an index named emp_idx exists on the table. Replace the names with objects from your own schema.

Key Points

  • The plan is a prediction. The explained plan can differ from the plan used at run time. The cause can be a difference between the explain environment and the execution environment.
  • Bind variables are a special case. With bind variables in general, the EXPLAIN PLAN output might not represent the real execution plan.
  • You need privileges on both sides. You must be able to insert rows into the plan table. You must also have the privileges needed to run the statement you explain. To read the plan, you must be able to query the plan table.
  • Cost is a comparison unit. The cost is an internal unit that the plan displays only so you can compare plans. You cannot tune the cost value itself. Cost does not equal time, and you cannot compare the costs of different queries with each other.
  • The plan table holds more than steps. It also holds optimization data, such as the cost and cardinality of each operation.

Insights and Best Practices

Read the plan for the choices the optimizer made

The optimizer looks at many access methods, such as a full table scan or an index scan. It looks at join methods such as nested loops and hash joins. It looks at different join orders and at possible query transformations. It picks the plan with the lowest cost. When you read a plan, ask which of these choices appear and whether the statistics support them.

A full table scan is a good example. If statistics show that 80 percent of employees are managers, a full table scan can be the most efficient choice. If statistics show that very few employees are managers, reading an index and then reading the table rows by rowid can be cheaper. A scan that looks wrong may be right, and a scan that looks fine may rest on old statistics. Check the statistics before you change the query.

Look at join methods and join order

Nested loops and hash joins suit different row counts. In a nested loops join, the number of rows in the outer table sets how often the database must probe the inner table. A nested loops join is a good choice when the database can avoid scanning a large part of the inner table, because the join condition filters its rows. If few rows are filtered, scanning the right table in a hash join is the better choice. The optimizer also tries more than one join order and abandons the ones that cost more.

When you review a plan with three tables, as in block 6, check which table drives the join and which join method each step uses. Then ask if the order matches what you know about the data.

Use statement IDs as a lab notebook

Give every test its own label, such as before_index and after_index. Display both plans. Record the cost and the steps. Do this before and after each change, and you keep a written history of what each change did. Cost values are only useful inside one query, so compare the same query across versions and not one query against another.

Pick the display tool that fits

Use utlxpls for a quick view of a serial plan. Use utlxplp when the statement runs in parallel. Use DBMS_XPLAN.DISPLAY when you need to pick the plan table, the statement ID or the format. To see the plan of a statement that is already in the cursor cache, use DBMS_XPLAN.DISPLAY_CURSOR. This function displays the explain plan of any cursor loaded in the cursor cache, and it can also report plan statistics such as I/O, memory and timing.

When to Use This Check

  • Before you release a new query to production.
  • When a query is slow and you need to see how the optimizer runs it.
  • After you add or drop an index and want to confirm the plan changed.
  • When you test two versions of one query and want to compare them.
  • After a statistics refresh, if a query changes speed.
  • When you cannot run a heavy statement but need to know its likely cost.

Troubleshooting Common Issues

The plan table does not exist. If you omit INTO, the database expects PLAN_TABLE in your schema. The sample table is defined in the script utlxplan.sql on the distribution media. Its exact name and location depend on your operating system. Your own output table must have the same column names and data types as the sample.

You get a privilege error. Check the three rights listed in Key Points: insert on the plan table, the right to run the explained statement, and query on the plan table.

The plan looks wrong for a statement with bind variables. The explained plan may not match the real one. Compare it with the plan of the cursor that actually ran, by using DBMS_XPLAN.DISPLAY_CURSOR.

Old rows confuse the output. If a label is reused, the plan table can hold rows for the same ID from earlier runs. Remove the old rows before you explain again with that label.

Two plans show very different cost, but run time is the same. Cost does not equate directly to time. Measure the real run time before you act on a cost difference.

References

Posts in this series