Execution Environment:
     

Access Privileges:
     The utlxplan.sql script needs to be run to create the table "plan_table"
     in the schema of the user that is going to execute the explain plan.
Usage:
     sqlplus / @[SCRIPTFILE]

Instructions:
     Add the query whose explain plan is to be generated in the section marked
     in the script.

PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text 
editors, e-mail packages, and operating systems handle text formatting (spaces, 
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.

The following script is intended to assist application developers and  
performance specialists in determining the execution path of a given SQL  
statement without having to trace and tkprof the statement. 
 
To use, you will make a copy of this file, then place the sql statement to be  
analyzed in the copy and run the script. 
 
Sample Output
=============

OPERATIONS OPTIONS OBJECT_NAME
------------------------- --------------- -----------------------
  MERGE JOIN			 
     SORT            		JOIN 
       TABLE ACCESS             FULL   		 DEP 
     SORT 			JOIN		 
       TABLE ACCESS             FULL		 EMP

[Include references to  FAQ, Troubleshooting guide, and Current issues
Articles from Top Tech Docs or other relevant references.]

SET ECHO off 
REM NAME:   TFSXPLAN.SQL 
REM USAGE:  See PURPOSE 
REM ------------------------------------------------------------------------ 
REM REQUIREMENTS: 
REM    utlxplan.sql must be run prior to this script! 
REM ------------------------------------------------------------------------ 
REM AUTHOR:  
REM    Craig A. Shallahamer, Oracle USA      
REM ------------------------------------------------------------------------ 
REM PURPOSE: 
REM    The following script is intended to assist application developers and 
REM    performance specialists in determining the execution path of a given  
REM    SQL statement without having to trace and tkprof the statement. 
REM 
REM    To use, you will make a copy of this file, then place the SQL 
REM    statement to be analyzed in the copy and run the script. 
REM 
REM    EXAMPLE 
REM 
REM    1. $ cp $path/explbig.sql x.sql 
REM    2. modify x.sql with your SQL. 
REM    3. SQL>@x.sql 
REM  
REM ------------------------------------------------------------------------ 
REM EXAMPLE: 
REM     OPERATIONS                OPTIONS         OBJECT_NAME 
REM 
REM    ------------------------- --------------- -----------------------  
REM      MERGE JOIN 
REM        SORT                       JOIN 
REM          TABLE ACCESS             FULL            DEPT 
REM        SORT                       JOIN 
REM          TABLE ACCESS             FULL            EMP 
REM  
REM ------------------------------------------------------------------------ 
REM DISCLAIMER: 
REM    This script is provided for educational purposes only. It is NOT  
REM    supported by Oracle World Wide Technical Support. 
REM    The script has been tested and appears to work as intended. 
REM    You should always run new scripts on a test instance initially. 
REM ------------------------------------------------------------------------ 
REM Main text of script follows: 
 
col operation 	format	a13	trunc 
col options	format	a15	trunc 
col object_name	format	a23	trunc 
col id		format	9999 
col parent_id	format	9999 
col position	format	9999 
col operations	format	a25 
 
explain plan 
set statement_id = 'x' 
into plan_table 
for  
<> 
 
select lpad(' ',2*level) || operation operations,options,object_name 
from plan_table 
where statement_id = 'x' 
connect by prior id = parent_id and statement_id = 'x' 
start with id = 1 and statement_id = 'x' 
order by id; 
 
rollback; 

EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,
PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS
ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS
THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR
RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR
OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE
NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.

ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY
TIME WITHOUT NOTICE.

IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,
DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN
CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.

SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.
ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.