explain plan template again.sql
— explain template
set pages 5000
set pause off
undef statement
set verify off feedback off
explain plan
set statement_id = ‘mdw’
into plan_table
for
— insert sql here
—
—
spool exp1.lst
select operation||’ ‘||to_char(timestamp,’DD/MM HH24:MI:SS’)||’ ‘||optimizer||’ cst:’||to_char(cost)||’ crd:’||to_char(cardinality) Plan
from plan_table
where statement_id = ‘mdw’
and id = 0
/
select rpad(to_char(id),2*level)||operation||’ ‘||options||’ ‘||object_name
 ||decode(cost,null,decode(partition_start,null,null
                          ,   ‘ strt ‘||partition_start
                              ||’ stp ‘||partition_stop)
              ,     decode(partition_id,null,”
                      ,    ‘ P’||partition_id)
                     ||’ cst:’||to_char(cost)||’ rws:’||cardinality
         )
           Plan
from plan_table
where statement_id = ‘mdw’
connect by prior id = parent_id
and       prior statement_id = ‘mdw’
start with id = 1
/
spool off
delete from plan_table
where statement_id = ‘mdw’
/
set verify on feedback on pause on pause ‘Any Key…>’
—
— End of File
—
Discussion ¬