rem *********************************************************************
rem *                                                                   *
rem * Name     : explain.sql                                            *
rem * Synopsis : Script shows explain plan without running statement.   *
rem *            Note that you must have a PLAN_TABLE to run this.      *
rem * Source   : http://www.oracle-dba.fr.pl                            *
rem *                                                                   *
rem *********************************************************************

save buffer_temp repl

truncate table plan_table
/

get buffer_temp nolist

1
c//explain plan set statement_id = 'XPLAIN' for /
/

select     lpad(' ', 2*(level - 1))||
           operation||' '||
           options||' '||
           object_name||' '||
           object_type||' '||
           object_instance||' '||
           decode(id, 0, 'Cost = '||position) "Query Plan"
from       plan_table
start with id = 0
connect by prior id = parent_id;

get buffer_temp nolist