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_plan7.sql
REM
REM <SQLDIR_GRP>UTIL</SQLDIR_GRP>
REM
REM Author:
REM 	Jonathan Lewis
REM	JLEWIS
REM
REM Purpose:
REM	<SQLDIR_TXT>
REM	Purpose:
REM		q and d to execute explain plan (Oracle 7.3)
REM	Preparation:
REM	        Run $ORACLE_HOME/rdbms/admin/utlxplan.sql as SYSTEM
REM	        Create public synonym plan_table for plan_table
REM	        Grant all on plan_table to public
REM	        Create an index (id,parent_id) on plan_table
REM
REM	Use:
REM	        Put the guilty SQL statement (with ';') into a file called
REM	                target.sql
REM	        Start explain7.sql
REM
REM	        The script displays the current audit id, then
REM	        the execution path, simultaneously writing the
REM	        execution path to a file identified by the audit id.
REM	</SQLDIR_TXT>
REM
REM Usage:
REM	c_plan7.sql
REM
REM Example:
REM	c_plan7.sql
REM
REM
REM History:
REM	??-??-????	JLEWIS		Created
REM	08-01-2001	VMOGILEV	Added to DBATOOLZ Library
REM
REM

set pagesize 24
set linesize 180
set trimspool on
set verify off

set def =
set def &

column plan             format a160     heading "Plan"

column id               format 999      heading "Id"
column parent_id        format 999      heading "Par"
column position         format 999      heading "Pos"
column object_instance  format 999      heading "Ins"

column state_id new_value m_statement_id

select userenv('sessionid') state_id from dual;

explain plan
set statement_id = '&m_statement_id'
for
@target


set feedback off
spool &m_statement_id

select
        id,
        parent_id,
        position,
        object_instance,
        rpad(' ',2*level) ||
        operation || ' ' ||
        decode(optimizer,null,null,
                '(' || lower(optimizer) || ') '
        )  ||
        object_type || ' ' ||
        object_owner || ' ' ||
        object_name || ' ' ||
        decode(options,null,null,'('||lower(options)||') ') ||
        other_tag || ' ' ||
        decode(cost,null,null,
                'Cost (' || cost || ',' || cardinality || ',' || bytes || ')'
        )       plan
from
        plan_table
connect by
        prior id = parent_id and statement_id = '&m_statement_id'
start with
        id = 0 and statement_id = '&m_statement_id'
order by
        id
;

rem     *************************************
rem
rem     Dump remote code, PQ slave code etc.
rem     but only for lines which have some
rem
rem     *************************************

set long 20000

select
        id, object_node, other
from
        plan_table
where
        statement_id = '&m_statement_id'
and     other is not null
order by
        id;

rollback;

spool off