SQL Script Explain Plan

Updated: 02-Jun-2005
Version: 0.0

Description

Explain Plan for Version 7 Databases of Oracle

Parameters

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

rem Author : Duncan Ainsworth
rem 

set pages 200

truncate table plan_table;
alter session set partition_view_enabled = true;

set doc off

spool explain.lst

SET ECHO ON
explain plan set statement_id = 'EXPL' for
select * from hr.employees
/


/* Alternative 1 Use prior 7.3  
set echo off
column operation   format a16
column options     format a15
column object_name format a20
column id          format 99
column query       heading "Query Plan" format a80 
select lpad(' ',2*(level-1))||operation||' '||options||' '
       ||object_name||' '
       ||decode(object_node,'','','['||object_node||'] ')
       ||decode(optimizer,'','','['||optimizer||'] ')
       ||decode(id,0,'Cost = '||position) query
from   plan_table
start with id = 0
connect by prior id = parent_id
order by id;
*/

/* Alternative 2 - Use with 7.3 
set echo off
column operation format a16
column options format a15
column object_name  format a20
column id  format 99
column query heading "Query Plan" format a80
select lpad(' ',2*(level-1))||operation||' '||options||' '||object_name||' '
       ||decode(object_node,'','','['||object_node||'] ')
       ||decode(OPTIMIZER,'','','['||OPTIMIZER||'] ')
       ||decode(id,0,'Cost='||position,
                     decode(COST,'','',' Cost='||COST||' '
                            ||decode(id,0,'','Card='||CARDINALITY||' ')
                            ||decode(id,0,'','Bytes='||BYTES) 
                           )               ) query
from plan_table
start with id = 0
connect by prior id = parent_id
order by id
*/

/* Use of the OTHER column 
set echo off     
set long 200
column query       heading "Query Plan" format a45 
column other       heading 'Other' format a34
column other_tag   heading 'Other Tag' format a34
select lpad(' ',2*(level-1))||operation||' '||options||' ' 
       ||object_name||' '
       ||decode(object_node,'','','['||object_node||'] ') query,       other_tag
from   plan_table
start with id = 0    connect by prior id = parent_id
order by 1
*/

COL QUERY_PLAN FORMAT A10
COL OPERATION FORMAT A30
column options format a15
column object format a15
column id           format 99 head 'ID'
column parent_id    format 99 head 'P-Id'
column position     format 99 head 'Pos'
SELECT id
       PARENT_ID,
       LPAD(' ',(LEVEL-1)*2) || OPERATION   "OPERATION", 
                                OPTIONS     "OPTIONS", 
                                OBJECT_NAME "OBJECT"
       ,decode(id,0,'Cost='||position) "QUERY_PLAN"
FROM PLAN_TABLE
START WITH UPPER(STATEMENT_ID) = UPPER('EXPL') 
CONNECT BY PRIOR ID = PARENT_ID AND UPPER(STATEMENT_ID) = UPPER('EXPL')
AND PARENT_ID IS NULL
ORDER BY ID
/

set feedback on
set echo on

/* Oracle 8i and 9i can use utlxpls.sql (serial explain) and utlxplp (parallel explain) */

REM @utlxpls.sql
REM @utlxplp

spool off

Related Links

Oracle 10g: (SQL Script) Explain Plan Template

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links