explain plan

explain plan statement will generate execution plan for sql statement in oracle against current environment ( statistics, optimizer settings)

explain plan statement will populate the result to plan table
public synonym plan_table
real tale sys.plan_table$

any user can generate its one plan table by run below script
@?/rdbms/admin/utlxplan.sql


resulted execution plan can be pull out by run below script
@?/rdbms/admin/utlxpls.sql

1. UTLXPLS.SQL This script displays the plan table output for serial processing.

2. UTLXPLP.SQL This script displays the plan table output including parallel execution columns.

or use below statement

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


the execution plan from explain plan statement may not be the exact running plan if environment changed when a statement is executed.

real execution plan info can be found in below dynamic views
v$sql_plan and V$SQL_PLAN_STATISTICS and V$SQL_PLAN_STATISTICS_ALL


explain plan statement examples
1. explain plan for STATEMENT
2. EXPLAIN PLAN
SET STATEMENT_ID = 'st1' FOR statement;
3. EXPLAIN PLAN
INTO my_plan_table
FOR statement ;
4. EXPLAIN PLAN
SET STATEMENT_ID = 'st1'
INTO my_plan_table
FOR statement;





###############
DBMS_XPLAN.DISPLAY procedure

This procedure accepts options for displaying the plan table output. You can specify:

*

A plan table name if you are using a table different than 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, and TYPICAL, ALL,

Some examples of the use of DBMS_XPLAN to display PLAN_TABLE output are:

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

SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));