Explaining and Displaying Execution Plans
An execution plan is the sequence of operations that the database performs to run a SQL statement.

Using the EXPLAIN PLAN Statement
The EXPLAIN PLAN statement enables you to examine the execution plan that the optimizer chose for a SQL statement.

EXPLAIN PLAN Statement

The EXPLAIN PLAN statement displays execution plans that the optimizer chooses for SELECT, UPDATE, INSERT, and DELETE statements.

EXPLAIN PLAN output shows how the database would have run the SQL statement when the statement was explained.
Because of differences in the execution environment and explain plan environment, the explained plan can differ from the actual plan used during statement execution.

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

Global temporary table PLAN_TABLE$ in the SYS schema,

select owner,table_name,temporary,duration from dba_tables where table_name like 'PLAN_TABLE$';

OWNER             TABLE_NAME       TEMPORARY             DURATION
-------------------- -------------------- ------------------------------ -----------------------------------------------
SYS             PLAN_TABLE$      Y                 SYS$SESSION

PLAN_TABLE  as a synonym

SQL> select owner,object_name,object_type from dba_objects where object_name like 'PLAN_TABLE%';

OWNER             OBJECT_NAME              OBJECT_TYPE
-------------------- -------------------- ----------------------------------------
SYS                  PLAN_TABLE$              TABLE
PUBLIC             PLAN_TABLE               SYNONYM

All necessary privileges to PLAN_TABLE are granted to PUBLIC. Consequently, every session gets its own private copy of PLAN_TABLE in its temporary tablespace.  

SQL> select owner,privilege,grantee,table_name from dba_tab_privs where table_name like 'PLAN_TABLE%';

OWNER       PRIVILEGE  GRANTEE     TABLE_NAME
---------- ---------- ---------- --------------------
SYS       DELETE     PUBLIC     PLAN_TABLE$
SYS       INSERT     PUBLIC     PLAN_TABLE$
SYS       SELECT     PUBLIC     PLAN_TABLE$
SYS       UPDATE     PUBLIC     PLAN_TABLE$

When we issue EXPLAIN PLAN statement it’s data populated in session’s PLAN_TABLE.
So to fetch data we can user DBMS_XPLAN.DISPLAY Function.

Required Privileges to CHECK VALUES FROM PLAN_TABLE using DBMS_XPLAN

GRANT SELECT ON v_$session TO user;
GRANT SELECT ON v_$sql_plan_statistics_all TO user;
GRANT SELECT ON v_$sql_plan TO user;
GRANT SELECT ON v_$sql TO user;

Displaying Execution Plans

The easiest way to display execution plans is to use DBMS_XPLAN display functions or V$ views.
After you have explained the plan, use the following SQL scripts or PL/SQL package provided by Oracle Database to display the most recent plan table output:

1.DBMS_XPLAN.DISPLAY table function
2.utlxpls.sql
This script displays the plan table output for serial processing
3.utlxplp.sql
This script displays the plan table output including parallel execution columns.

DBMS_XPLAN Display Functions

You can use the DBMS_XPLAN display functions to show plans.

The display functions accept options for displaying the plan table output. You can specify:

A plan table name if you are using a table different from PLAN_TABLE
A statement ID if you have set a statement ID with the EXPLAIN PLAN
A format option that determines the level of detail: BASIC, SERIAL, TYPICAL, ALL, and in some cases ADAPTIVE
 

Display Functions	Notes
DISPLAY	This table function displays the contents of the plan table.
DISPLAY_AWR	This table function displays the contents of an execution plan stored in AWR.
DISPLAY_CURSOR	This table function displays the explain plan of any cursor loaded in the cursor cache. In addition to the explain plan, various plan statistics (such as. I/O, memory and timing) can be reported (based on the V$SQL_PLAN_STATISTICS_ALL VIEWS).
DISPLAY_PLAN	This table function displays the contents of the plan table in a variety of formats with CLOB output type.
DISPLAY_SQL_PLAN_BASELINE	This table function displays one or more execution plans for the specified SQL handle of a SQL plan baseline.
DISPLAY_SQLSET	This table function displays the execution plan of a given statement stored in a SQL tuning set.