How to Get the Explain Plan of a Statement  
Author: James L. Colestock

Step 1: There must first be a PLAN_TABLE accessible to the user:  
            
DBA can install via the ${ORACLE_HOME}/rdbms/admin/utlxplan.sql script




Step 2:  Issue the EXPLAIN PLAN statement for the SQL Statement in Question

EXPLAIN PLAN SET STATEMENT_ID = '<Your Statment ID Here>'                
FOR <Your SQL Statement to Explain Here>;

Example: 

EXPLAIN PLAN SET STATEMENT_ID = '1' FOR SELECT * FROM USER_TABLES;
 


Step 3: Query the EXPLAIN PLAN from the PLAN_TABLE

Example:

select lpad(' ',2*(level-1))||operation||' '||options||' ' ||object_name||' ' 
||decode(object_node,'','','['||object_node||'] ') query, other 
from plan_table
where statement_id = '1'
start with id = 0
connect by prior id = parent_id;