vsbabu.org

Oracle Explain Plan

A shell script wrap for Oracle's explain plan for SQLs.

#! /bin/ksh

# Change this to your ORACLE connection
_CONNECT=userid/passwd
# Change this to suite your temporary directory
_tmpfil=/tmp/seeplan.$$

#----------- No need to change below this line -----------------
_PROG=$0
usage()
{
	echo "Usage : ${_PROG} "
	echo "        filename - the name of file having single SQL statement"
	echo "        reads the standard input if no filename is given"
	echo "        logs in and shows the explain plan"
	echo " Author:Vattekkat Satheesh Babu"
}

shoerr()
{
	echo "ERROR : $_MESSAGE"
	exit 1
}


if [ $# -ne 1 ]
then
	usage
	touch $_tmpfil
	[ $? -ne 0 ]&&(_MESSAGE='Cannot create file in /tmp';shoerr)
	echo "Type in the script , end by CTRL-D"
	echo ""
	cat > $_tmpfil
	_SCRIPT=$_tmpfil
else
	_SCRIPT=$1
fi

[ ! -s  $_SCRIPT ]&&(_MESSAGE='Script file in error';shoerr)

_SCRIPT_plan=/tmp/seeplan_$$.sql
touch $_SCRIPT_plan
[ $? -ne 0 ]&&(_MESSAGE='Cannot create file in /tmp';shoerr)
cat>$_SCRIPT_plan<<FINCAT
SET PAGESI 0
SET LINESI 80
SET VERIFY OFF
SET FEEDBACK OFF
SET ECHO OFF
EXPLAIN PLAN SET STATEMENT_ID='CPLAN$$'
	INTO PLAN_TABLE
	FOR 
FINCAT
cat $_SCRIPT >> $_SCRIPT_plan
[ -f $_tmpfil ]&&rm -f $_tmpfil

cat>>$_SCRIPT_plan<<FINCAT1
SELECT LPAD(' ',2*LEVEL)||OPERATION||''||OPTIONS||' '||
	   OBJECT_NAME EXPLAIN_PLAN
FROM PLAN_TABLE
WHERE STATEMENT_ID='CPLAN$$' 
CONNECT BY PRIOR ID=PARENT_ID AND
			STATEMENT_ID='CPLAN$$'
START WITH ID=1 ;
DELETE FROM PLAN_TABLE WHERE STATEMENT_ID='CPLAN$$' ;
COMMIT;
FINCAT1


sqlplus -s $_CONNECT<<FINSQL
start $_SCRIPT_plan
exit;
FINSQL

rm -f $_SCRIPT_plan