Get the Execution Plan from/using PLAN_TABLE

PLAN_TABLE is automatically created as a global temporary table in the SYS  schema, creating a public synonym for it, and then granting all necessary privileges to PUBLIC.

PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans.

PLAN_TABLE just shows you a plan that might not be the one chosen by the optimizer.

• PLAN_TABLE:

      – Is automatically created to hold the EXPLAIN PLAN output.

      – You can create your own using utlxplan.sql script if you want to keep the execution plan information for a long term..

      – Advantage: SQL is not executed

      – Disadvantage: May not be the actual execution plan

• PLAN_TABLE is hierarchical.

• Hierarchy is established with the ID and PARENT_ID columns.

It is recommended that you drop and rebuild your local PLAN_TABLE table after upgrading the version of the database because the columns might change. This can cause scripts to fail or cause TKPROF to fail, if you are specifying the table.

Example:

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'demo' FOR SELECT * FROM emp
2 WHERE ename = 'KING';
Explained.
SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> select * from table(DBMS_XPLAN.DISPLAY());
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 37 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='KING')