SQL Script

Explain Plan Template

Updated : 21-Mar-2002
Version : 1.4

Description

The old faithful explain plan script. No Oracle professional should be without it. Replace the highlighted text with the your SQL statement to explain.

This script now contains different formats to use for all Oracle Versions. Uncomment out the version for your database.

Parameters

None.

SQL Source

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
< INSERT YOUR QUERY HERE>
/

/* Oracle 8i can use utlxpls.sql */

@utlxpls.sql

/* Oracle 7.3+ can use autotrace to get a plan for any queries run */

set autotrace trace explain statistics;

/* 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

spool off