Get the Execution Plan from/using EXPLAIN PLAN

The EXPLAIN PLAN command enables you to view the execution plan that the optimizer might use to execute a SQL statement. This command is very useful because it outlines the plan that the optimizer may use and inserts it in a table called PLAN_TABLE without executing the SQL statement. This command is available from SQL*Plus or SQL Developer.It does not execute the statement, but simply produces the plan that may be used, and inserts this plan into a table.

If you execute the EXPLAIN PLAN in SQL*Plus command, you can then SELECT from the PLAN_TABLE to view the execution plan.

Command:  

This command inserts the execution plan of the SQL statement in the plan table and adds the optional demo name tag for future reference.

SQL> EXPLAIN PLAN SET STATEMENT_ID='demo' FOR select * from emp;
Explained.
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
---------------------------------------------------------
| Id  | Operation                           |   Name  |
---------------------------------------------------------
|   0 | SELECT STATEMENT             |             |
|   1 |  TABLE ACCESS FULL            | EMP      |
---------------------------------------------------------
8 rows selected.
SQL>

Formatted Select of PLAN_TABLE for EXPLAIN  PLAN command.........

set echo off

column operation   format a20

column options     format a16

column object_name format a20

column id          format 99

column query       heading "Query Plan" format a80

select lpad(' ',2*(level-1))||operation||' '||options||' '

       ||object_name||' '

       ||decode(object_node,'','','['||object_node||'] ')

       ||decode(optimizer,'','','['||optimizer||'] ')

       ||decode(id,0,'Cost = '||position) query

from   plan_table

start with id = 0

connect by prior id = parent_id;

set echo on