Explain Plan

Une utilisation de explain plan quasi automatique. Passez l'ordre SQL. Appelez la procedure.

set echo off
set feedback off
set recsep off
set arraysize 1;

column     ID        format 99;
column    PAR        format a6     heading PAR;
column    POSITION    format 9990    heading POS;
column    OPERATION    format a20;
column    OPTIONS        format a14;
column    OBJECT_NAME    format a14;

save explaining.sql replace
savepoint explanation;
delete from PLAN_TABLE;
explain plan for
@explaining.sql
set linesize 100
set pagesize 0
prompt
select decode(id,0,operation,lpad(' ',(1*level)-1) || OPERATION) OPERATION,
    OPTIONS, decode(id,0,OPTIMIZER,OBJECT_NAME) OBJECT_NAME,
    ID, decode(id,0,'Cost = ',PARENT_ID) PAR, POSITION
from PLAN_TABLE
connect by prior ID = parent_ID start with ID = 0
order by ID;
prompt

Select LPad(' ', 2*(Level-1)) || Level || '.' || Nvl(Position,0) || ' ' ||
Operation || ' ' || Options || ' ' || Object_Name || ' ' ||
Object_Type || ' ' || Decode(id, 0, Statement_Id ||' Cost = ' || Position)
From Plan_Table
Start with id = 0
Connect By Prior Id = Parent_Id ;

prompt;

rollback to explanation;
get explaining.sql nolist
set recsep wrapped
set feedback on
set echo on

New