Reading an execution plan

SQL says what you want; the optimizer decides how: which table to read first, whether to use an index, how to join. The plan is that decision written down. Reading it is the single most useful performance skill there is, and it's less scary than it looks.

Getting a plan

explain plan for
select * from cr_players where player_id = 101;

select * from table(dbms_xplan.display);

EXPLAIN PLAN stores the plan in PLAN_TABLE without running the query; DBMS_XPLAN.DISPLAY prints it. (In SQL Developer and SQLcl, F10 / explain plan for does the same. For a query you've actually run, DBMS_XPLAN.DISPLAY_CURSOR shows the plan it really used, with real row counts if you add the /*+ gather_plan_statistics */ hint.)

The simplest plan: find one player by key

This is the real plan from our 26ai database:

--------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |     1   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| CR_PLAYERS    |     1 |     1   (0)|
|   2 |   INDEX UNIQUE SCAN         | CR_PLAYERS_PK |     1 |     0   (0)|
--------------------------------------------------------------------------

Read it from the inside out:

1. INDEX UNIQUE SCAN CR_PLAYERS_PK: look up 101 in the primary key index, get the row's address (its ROWID).
2. TABLE ACCESS BY INDEX ROWID CR_PLAYERS: go straight to that address and fetch the row.
3. SELECT STATEMENT: hand it back.

The rules: indentation is parent and child; a child runs before its parent; siblings run top to bottom; rows flow upward. The numbered tree shows the run order; the text version is behind the "DBMS_XPLAN text" toggle.

The same query on 19c Autonomous

--------------------------------------------------------------------------------
| Id  | Operation                         | Name          | Rows  | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |               |     1 |     1   (0)|
|   1 |  PX COORDINATOR                   |               |       |            |
|   2 |   PX SEND QC (RANDOM)             | :TQ10001      |     1 |     1   (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID    | CR_PLAYERS    |     1 |     1   (0)|
|   4 |     BUFFER SORT                   |               |       |            |
|   5 |      PX RECEIVE                   |               |     1 |     0   (0)|
|   6 |       PX SEND HASH (BLOCK ADDRESS)| :TQ10000      |     1 |     0   (0)|
|   7 |        PX SELECTOR                |               |       |            |
|   8 |         INDEX UNIQUE SCAN         | CR_PLAYERS_PK |     1 |     0   (0)|
--------------------------------------------------------------------------------

Same two important steps (highlighted), wrapped in PX operations: our Autonomous Database runs queries in parallel by default, so it adds steps to hand rows between parallel workers. For one row it's pure overhead, but the plan is correct. Plans depend on the database, its settings and its statistics, not just on the SQL. That's why the test on this page checks only the steps that matter.

The test case

Explain the one-player lookup and list just its access steps: how each table or index is read.

Setup

delete from plan_table where statement_id = 'D140_PK';

Setup

explain plan set statement_id = 'D140_PK' for
select * from cr_players where player_id = 101;

Expected result

Querying PLAN_TABLE

select operation, options, object_name
from   plan_table
where  statement_id = 'D140_PK'
and    operation in ('TABLE ACCESS', 'INDEX')
order  by id;

PLAN_TABLE has one row per step: ID, PARENT_ID, DEPTH, OPERATION, OPTIONS, OBJECT_NAME, the optimizer's estimated CARDINALITY (rows) and COST. DBMS_XPLAN just formats it.

The words you'll see most

19c and 26ai

The tools are the same on both. The plans often aren't: next page.

Gotchas

• EXPLAIN PLAN guesses; the real run might differ. With bind variables, adaptive plans and changing statistics, use DBMS_XPLAN.DISPLAY_CURSOR for the plan that actually ran.
• Cost is the optimizer's estimate in its own units. Compare the costs of two plans for the same query, never across different queries.
• Rows is an estimate too. When the estimate is far from reality (check with gather_plan_statistics), the plan is usually wrong because of it: stale or missing statistics.