Oracle's SQL statement EXPLAIN PLAN can be given an SQL statement in order for a user to query such an execution plan:
EXPLAIN PLAN FOR <sql statement>;
By default, explain plan writes the result into a table named plan_table.
However, the destination table can changed with the into … clause, see below. It is also possible to give a plan
In order to make it easier to identify a plan in the plan table, a statement id can be provided:
EXPLAIN PLAN FOR
SET STATEMENT ID = 'long-running-query'
FOR
SELECT …
It turns out that the installation script catplan.sql creates a public synonym plan_table that points to a global temporary table (GTT) named plan_table$. Because plan_table$ is a GTT, every session has its own version of the contents of plan_table$.
explain plan is a DML statement, so the modifications made by this statement are not committed.
Show execution plan with DBMS_XPLAN
The result that is written into the plan table can be displayed with dbms_xplan.display):
SQL> explain plan for select * from dual connect by level < 10;
SQL> select * from table(dbms_xplan.display);
…
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
|* 1 | CONNECT BY WITHOUT FILTERING| | | | | |
| 2 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Show execution plan with a SELECT statement
It is also possible to query a plan directly from the plan_table. This boils down to a variant of
select
lpad(' ', depth*2) || operation || ' ' || options op,
object_name
from
plan_table
where
plan_id = (select max(plan_id) from plan_table)
order by
id
;
Of course, in the real world, more columns that are found in plan_table would probably be added.
Specifying a result table
It is also possible to specify a result table that is different from plan_table:
explain plan
-- set statement_id = 'st1'
into
tq84_plan
for
select
foo, bar, baz
from
tab;
If then the same statement is executed but with the bind variables bound to different data types, Oracle might choose to execute the statement with a different plan.
See also
The Package dbms_xplan provides the table function display which formats the content of the plan table after executing an explain plan statement.