PLAN TABLE in Oracle

The PLAN_TABLE is a special table that shows how a SQL statement is executed. It's automatically created when you use the EXPLAIN PLAN command, which creates a report of the execution plan and stores it in the PLAN_TABLE. This table is available to all users, and can be used to display the results of the EXPLAIN PLAN statement.

You can use the script $ORACLE_HOME/rdbms/admin/utlxplan.sql to manually create a local PLAN_TABLE in your schema. 

To display the execution plan of a SQL statement stored in the PLAN_TABLE, you can use the DBMS_XPLAN package. First, use the EXPLAIN PLAN command to create a report and store it in the PLAN_TABLE. Then, use the DISPLAY function of the DBMS_XPLAN package to format and display the execution plan for the statement stored in PLAN_TABLE.

Displaying execution plan from PLAN_TABLE :

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'planid1' FOR SELECT * FROM emp WHERE last_name = 'John';

Explained.

SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> select * from table(DBMS_XPLAN.DISPLAY());
Plan hash value: 1600171752

-------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name        | Rows  | Bytes | Cost (%CPU) | Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |             |     1 |    69 |    2  (0)   | 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED       | EMP         |     1 |    69 |    2  (0)   | 00:00:01 |
|*  2 |   INDEX RANGE SCAN                         | EMP_NAME_IX |     1 |       |    1  (0)   | 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("LAST_NAME"='John')

14 rows selected.

SQL>
    

In the above example, the EXPLAIN PLAN command inserts the execution plan of the SQL statement in PLAN_TABLE and adds the optional planid1 name tag for future reference.

The DISPLAY function of the DBMS_XPLAN package can be used to format and display the last statement stored in PLAN_TABLE.

Quiz

Question 1 :

EXPLAIN PLAN command executes the statement and inserts the plan used by the optimizer into a table called PLAN TABLE

TRUE
FALSE

Question 2 :

Which of the following is TRUE about a PLAN_TABLE?

The PLAN_TABLE is automatically created to hold the EXPLAIN PLAN output
You cannot create your own PLAN_TABLE
The actual SQL command is executed
The plan in the PLAN_TABLE is the actual execution plan

Quiz Results

You scored out of 2.

Question 1 Explanation

Explain Plan does not actually execute the statement, instead it generates only the execution plan and inserts it into the PLAN_TABLE.

Question 2 Explanation

A is Correct : Plan table is automatically created.

B is Incorrect : You can create your own plan table using the script utlxplan.sql.

C is Incorrect : The actual SQL command is not executed.

D is Incorrect : The plan in the PLAN_TABLE is not the actual execution plan. Optimizer may choose different plan based on the bind variable values used.