-- #############################################################################################
--
-- %Purpose: Displays the execution plan for a SQL DML statement
--
-- The SQL statement should be in a separate text file,
-- with either a ";" at the end of the line or a "/" on
-- the next line.  A plan_table table is required.
--
-- Usage: sqlplus user/pwd @explain filename
--
-- #############################################################################################
--
set feedback off arraysize 10 trimspool on linesize 1000
--
alter session set optimizer_percent_parallel = 100;
--
delete from plan_table;
commit;
--
set echo on
explain plan for
@&1
set echo off
--
col "Query Plan" for a70
--
select
  to_char(id,'999') ID,
  to_char(parent_id,'999') pt,
  initcap(
    lpad(' ',2*(level-1)) ||
    operation || ' ' ||
    options || ' ' ||
    decode(object_name,null,null,'of') || ' ' ||
    object_name || ' ' ||
    object_type || ' ' ||
    decode(id,0,'Cost = ' || ltrim(to_char(position,'999,999,999')))
    ) "Query Plan",
  to_char(cardinality,'999,999,999') "Row Count",
  substr(InitCap(other_tag),1,30) Other
from
  plan_table
start with id = 0 connect by prior id = parent_id
/
--
rollback;
--
set feedback on