
column sqlname noprint new_value sqlname
column myname new_value myname noprint
set feedback 2
set heading off
select '&1' || decode(instr('&1','.sql'), 0, '.sql', NULL) sqlname
,      userenv('TERMINAL') myname
from dual
/

set heading on
set feedback on

!echo "explain plan set statement_id='&myname' for" > $HOME/.plan.lst
!sed -e '/^\ *[Ii][Nn][Tt][Oo]/ d' -e 's/;$//' &sqlname >> $HOME/.plan.lst

get $HOME/.plan.lst
/

set pagesiz 10000
column position format a10 heading "POS"
column x format a149 trunc wrap
column y format a29 
column other format a48 wrap
column other_tag format a48 wrap

set linesiz 160
set long 50000
set verify off
set termout off
spool $HOME/.rp.lst

define color_normal="[0m"
define color_red="[31m"
define color_green="[32m"
define color_yellow="[33m"
define color_purple="[35m"

select 
	'&color_normal' || to_char(position) position,
--      -------padding and operation ----
	lpad(' ',2*(level-1))|| operation || 
--      -------type of scan - options----
	decode(options, null, null, 
		'FULL', ' &color_red(' || options || ')&color_normal',
		' (' || options || ')') || 
--      ---------------table or index name------------------------------
	decode(object_name, null, null, ' of ''' || object_name || '''') ||
	decode(partition_start,
		null, null,
		partition_stop, ' PARTITION ' || partition_start || ' ',
		' PARTITION ' || partition_start || ':' || partition_stop||' ')||
	decode(level, 
		1,	
--      -------------show optimizer mode on first line------------------
		decode(optimizer, null, null, 
				' &color_yellow(Optimizer=''' || optimizer || 
				''')&color_normal' ),
--      -------------show row count of expected rows read---------------
		decode(cardinality, null, null, ' &color_purple(' ||
				to_char(cardinality,'FM999,999,999') || 
				' rows)&color_normal' )) ||
--      -------------change colour for parallel query options-----------
	decode(other_tag,'PARALLEL_TO_SERIAL','&color_red','&color_green') x,
	' ' y, other 
from plan_table
where statement_id = '&myname'
start with id=0 and statement_id = '&myname' 
connect by prior id = parent_id 
and prior statement_id = statement_id
/

prompt &color_normal
set termout on
spool off


host sed -e 's/\ *$//g' -e '/^$/ d' $HOME/.rp.lst
delete from plan_table where statement_id = '&myname'
/

set linesiz 80
!rm $HOME/.plan.lst
