Explain Plan

* Explain Plan is a statement that lets you to have execution plan for any SQL statement without actually executing it. You will be able to examine the execution plan by querying the plan table.

* A Plan table holds execution plans generated by Execute plan statement. Typical name is plan_table but any name can be given for a plan table.

* utlxplan.sql file in $ORACLE_HOME/rdbms/admin contains script to create plan table.

* Privileges required for Explain Plan:
-> INSERT privilege for Explain Plan
-> EXECUTE privilege for statement execution
-> SELECT privilege on underlying table/view

Syntax:
EXPLAIN PLAN
[SET STATEMENT_ID = {string in single quotes}]
[INTO {plan table name}]
FOR
{SQL statement};

Output extraction:

select id, parent_id, LPAD('',LEVEL-1)||
operation ||' '||options operation, object_name
from plan_table
where statement_id = '&statement_id'
start with id=0
and statement_id = '&statement_id'
connect by prior id = parent_id
and statement_id = '&statement_id';

Explain Plan limitations:

The real plan that gets used may differ from what Explain Plan tells due to:
1. Optimizer stats, cursor sharing, bind variable peeking, dynamic instance parameters makes plans less stable.
2. Explain Plan does not peek at bind variables.
3. Explain Plan does not check library cache to see if the statement has already been parsed.
4. Explain Plan does not work for some queries
ORA-22905 : cannot access rows from a non-nested table item.