SQL Script Explain Plan Template

Updated: 21-Mar-2005
Version: 2.0

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

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

set pages 200

truncate table plan_table;

REM alter session set partition_view_enabled = true;

set doc off

spool explain.lst

SET ECHO ON
explain plan for
select * from hr.employees
/

set feedback on
set echo on

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

REM Use this for none parallel plans
@?\rdbms\admin\utlxpls.sql

REM Use this line form parallel query plans
REM @?\rdbms\admin\utlxplp

/* standard simple 8i explain plan. It is better to use new 8i/9i utlxpls that this now
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 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
*/

spool off

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