isitdevops.com/databases

— 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
—
SELECT MAX(MIN_ID)
FROM (  SELECT MIN(UNIQUEID) MIN_ID
       FROM BOBJ_LOGIN.ORDERS
       WHERE STATUS = :B2 AND UNIQUEID >= :B1
       UNION ALL
       SELECT MIN(UNIQUEID) MIN_ID
       FROM BOBJ_LOGIN.ORDERS
       WHERE STATUS = :B2 AND UNIQUEID > :B1 )
/
—
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 lpad(‘ ‘,2*level)||operation||’ ‘||options||’ ‘||object_name
  ||decode(cost,null,decode(partition_start,null,null
                           ,    ‘ strt ‘||partition_start
                               ||’ stp ‘||partition_stop)
              ,    ‘ 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
—