Explain the plan of SQL query with plan table in Oracle

Plan_table is a global temporary table present for all schemas to store the output of EXPLAIN PLAN.

For Manually Create the PLAN_TABLE at your own schema level:

@?\rdbms\admin\utlxplan.sql

Plan table output:

SQL> Explain plan for select * from employees;

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   107 |  7383 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  7383 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Store the plan in a different table then you can copy or rename the table. I changed default PLAN_TABLE name to MY_PLAN_TABLE;

RENAME PLAN_TABLE TO my_plan_table;

-- Use table name in EXPLAIN PLAN
SQL> EXPLAIN PLAN INTO my_plan_table
 FOR
 SELECT * FROM employees;
Explained.

SQL> select * from table(dbms_xplan.display('my_plan_table',null,'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1445457117

---------------------------------------
| Id  | Operation         | Name      |
---------------------------------------
|   0 | SELECT STATEMENT  |           |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |
---------------------------------------

8 rows selected.

SQL> select * from table(dbms_xplan.display('my_plan_table',null,'typical'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   107 |  7383 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  7383 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

8 rows selected.