REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM
REM File:
REM 	c_plan.sql
REM
REM <SQLDIR_GRP>TRACE UTIL</SQLDIR_GRP>
REM
REM Author:
REM 	Vitaliy Mogilevskiy
REM	VMOGILEV
REM	(vit100gain@earthlink.net)
REM
REM Purpose:
REM	<SQLDIR_TXT>
REM	This script prints execution plan for SQL which
REM	is already been explained in the PLAN_TABLE.
REM
REM	It will list all available SQL statement ID from PLAN_TABLE
REM	and prompt you for the one you need to pring the explain plan for.
REM
REM	To actually create an EXPLAIN PLAN for a SQL stmnt use the following:
REM	   SQL> explain plan
REM	   SQL> set statement_id = '<your_name>'
REM	   SQL> for
REM	   SQL> <your_sql_here>
REM	Before using explain plan you need to create PLAN_TABLE
REM	use ORACLE provided script for your platform ... RDBMS/ADMIN/UTLXPLAN.SQL
REM	</SQLDIR_TXT>
REM
REM Usage:
REM	c_plan.sql
REM
REM Example:
REM	c_plan.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM

clear breaks
clear columns
clear computes
set term on
set feedback on
set lines 132
set pages 900

-- col statement_id format a10 heading "SQL Name"
col query_plan format a100 heading "Execution Plan"

prompt Here is the list of available SQL statement ID's
prompt
prompt Statement ID's
prompt **************

set head off
set feedback off
set echo off

select distinct statement_id
from plan_table
/

set head on
set feedback on

prompt **************
accept 1 prompt "Enter Statement_id for Your SQL:"

select		rtrim(lpad(' ',2*level)||
		rtrim(operation)||' - '||
		rtrim(options)||' - '||
		object_name)			query_plan
from		plan_table
where		statement_id = '&&1'
connect by	prior id = parent_id
and		statement_id = '&&1'
start with	id = 0
order by	statement_id
/