set echo off
set doc off
set termout off
set verify off
--
--  explaindel.sql    
--
--  Explain Plan - Script to display the explain plan for a specific SQL statement
--  This script will explain plan a SQL statement, show the access paths and then delete the plan from the plan table.
--  You must plug in your sql statement below before running the scripts and specify a unique statementid.
--
--  You must have created your plan_table before running this script, see explain.sql for more information.
--
--  Usage: @oracle\tuning\ExplainDel
--
--  Specify a unique statementid   <---------------------------------
define statementid=test1
--
-- Create explain statement
set echo on
set feedback on
set pagesize 100
set linesize 100
set termout on
explain plan set statement_id '&&statementid' for 
-- !!!!! START OF SQL statement
select * from dual
;
-- !!!!! END OF SQL statement
-- Show access paths
column operation format a100;
select lpad('  ',2*(level-1))||operation|| ' ' || options  || ' ' || decode(to_char(id),'0',
  'COST= ' || nvl(to_char(position),'n/a'), object_name) || '   ' || id ||'-' ||
  nvl (parent_id, 0)||'-'|| nvl(position,0) || ' ' || substr(optimizer,1,20) operation
 from plan_table
 start with id =0
 and statement_id = '&&statementid'
 connect by prior id = parent_id
 and statement_id = '&&statementid'
;
--
-- Delete explain plan from plan table
delete from plan_table where statement_id = '&&statementid';
commit;
--undefine statementid