Script - Explain_plan

Para ver el plan de ejecución de una query recogido previamente mediante el comando Explain plan set statement_id=’test’ for ...

set lines 100 pages 100
select lpad (' ',2*level)||operation||' '||options
         ||' '||object_name  ||' '|| COST   q_plan
from plan_table
where statement_id='test'
connect by prior id = parent_id and statement_id='test'
start with id=1;

Script explain_plan distribuido por Oracle (utlxpls.sql), con algunos cambios relativos a formateo e inclusión del campo object_instance en la salida.

set linesize  200
Rem
Rem Display last explain plan
Rem
select '| Operation                         |  Name            |Rows|Bytes| Cost | Pos|Pstart| Pstop | '  as "Plan Table" from dual
union all
select '------------------------------------------------------------------------------------------------------------------' from dual
union all
select * from
(select /*+ no_merge */
       rpad('| '||substr(lpad(' ',1*(level-1))||operation||
            decode(options, null,'',' '||options), 1, 35), 36, ' ')||'|'||
       rpad(substr(object_name||' ',1, 17), 18, ' ')||'|'||
       lpad(decode(cardinality,null,'  ',
                decode(sign(cardinality-1000), -1, cardinality||' ',
                decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
                decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',
                       trunc(cardinality/1000000000)||'G')))), 4, ' ') || '|' ||
       lpad(decode(bytes,null,' ',
                decode(sign(bytes-1024), -1, bytes||' ',
                decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
                decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
                       trunc(bytes/1073741824)||'G')))), 5, ' ') || '|' ||
       lpad(decode(cost,null,' ',
                decode(sign(cost-10000000), -1, cost||' ',
                decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
                       trunc(cost/1000000000)||'G'))), 6, ' ') || '|' ||
       lpad(decode(object_instance, null, ' ', object_instance),4,' ')||'|'||
       lpad(decode(partition_start, 'ROW LOCATION', 'ROWID',
            decode(partition_start, 'KEY', 'KEY', decode(partition_start,
            'KEY(INLIST)', 'KEY(I)', decode(substr(partition_start, 1, 6),
            'NUMBER', substr(substr(partition_start, 8, 10), 1,
            length(substr(partition_start, 8, 10))-1),
            decode(partition_start,null,' ',partition_start)))))||' ', 7, ' ')|| '|' ||
       lpad(decode(partition_stop, 'ROW LOCATION', 'ROW L',
          decode(partition_stop, 'KEY', 'KEY', decode(partition_stop,
          'KEY(INLIST)', 'KEY(I)', decode(substr(partition_stop, 1, 6),
          'NUMBER', substr(substr(partition_stop, 8, 10), 1,
          length(substr(partition_stop, 8, 10))-1),
          decode(partition_stop,null,' ',partition_stop)))))||' ', 7, ' ')||'|'
as "Explain plan"
from plan_table
start with id=0 and timestamp = (select max(timestamp) from plan_table
                                 where id=0)
connect by prior id = parent_id
        and prior nvl(statement_id, ' ') = nvl(statement_id, ' ')
        and prior timestamp <= timestamp
order by id, position)
union all
select '------------------------------------------------------------------------------------------------------------------' from dual;