isitdevops.com/databases

REM Queries the table PLAN_TABLE, retrieves and formats the execution plan
REM for the specified statement ID
REM
REM This works for SQL statements previously run using the following syntax:
REM
REM explain plan set statement_id = ‘whatever’ for ;
REM

set pages 500;
set lines 100;
set feed off;
set time on;
set timing on;
set arraysize 1;

column “Object” format A30;
column “Execution path” format A50;

accept stid prompt ‘Statement ID: ‘

select
object_name “Object”,lpad(‘ ‘,2*level)||Operation||’ ‘||Options “Execution path”
from plan_table
where
statement_id = ‘&stid’
connect by prior ID = parent_id and statement_id = ‘&stid’
start with ID=1;

delete from plan_table;
commit;

set feed on;