I have provided below a short script that I keep in my SQL Plus directory, named Explain.SQL.  Any time I want to view the explain plan sequence for some difficult SQL select, I do the following in a SQL Plus session:

Type EXPLAIN PLAN FOR and press Enter.
Paste all the lines of the select statement from my PFE edit session.
Execute the statement -- (just press enter)
Type @EXPLAIN
The Explain.SQL script below does all the rest.  Now I just wish I could find an explanation somewhere that would tell me exactly what the Explain Plan output means, step by step.
------ Begin EXPLAIN.SQL script -----
set echo off
set feedback off
-- This select interprets the output of an
-- EXPLAIN PLAN FOR Select....   statement.
-- In order to run Explain Plan, the table Plan_Table must exist in
-- your schema.  If it does not, then run (from SQL Plus) the sql
-- script C:\ORAWIN\RDBMS71\ADMIN\utlxplan.sql to create it.
--
-- Each time you run Explain Plan, you should either run this script
-- OR be sure to delete everything in Plan_Table.  All you need is to
-- enter is:  delete plan_table;
-- Note that this script does this at the end.
-- If you don't clear out plan_table, then this script loops.  You
-- can stop the looping by pressing Ctrl-C.
--
update plan_table set statement_id='A' where statement_id is null;
--
Select lpad(' ',2*(level-1))||operation||' '||options||' '||object_name
     ||' '||decode(id,0,'Cost = '||position) "Query Plan"
   From plan_table
   Start with id = 0 and statement_id ='A'
   connect by prior id = parent_id and statement_id ='A';
delete plan_table;
commit;
set feedback on
set echo on
------ End of EXPLAIN.SQL script -----
 
 

If you don't have UTLXPLAN.SQL, just run this:

create table PLAN_TABLE (        statement_id    varchar2(30),
   timestamp      date,          remarks         varchar2(80),
   operation      varchar2(30),  options         varchar2(30),
   object_node    varchar2(128), object_owner    varchar2(30),
   object_name    varchar2(30),  object_instance numeric,
   object_type    varchar2(30),  optimizer       varchar2(255),
   search_columns numeric,       id              numeric,
   parent_id      numeric,       position        numeric,
   other  long);
 

Return to : Oracle Database, SQL, Application, Programming Tips