Here's a dumb bash script...
Steve Orr
--------------------------------------------------------------
#! /bin/bsh
#
# dotkprof.bsh <user> <pwd> <script.sql> <tkprof.out>
# Steve Orr 6/15/01
#
export ORACLE_HOME=whatever
export ORACLE_SID=whatever
ORACLE_BASE=whatever
UDUMPDIR="$ORACLE_BASE/Admin/$ORACLE_SID/udump"
CURDIR=`pwd`
# Validate parameters...
if [ "$3" ]; then
ORACLUSER=$1
ORAPASSWD=$2
SQLSCRIPT=$3
SQLBASENAME=`basename ${SQLSCRIPT} .sql`
else echo "Syntax = dotkprof.bsh user pwd script.sql [tkprof.out]"
exit 1
fi
# Validate script file exists...
if [ ! -f $SQLSCRIPT ]; then
echo "No such file as $SQLSCRIPT"
exit 1
fi
# tkprof output file is optional...
if [ "$4" ]; then
TKPROFOUT=$4
else
TKPROFOUT="${CURDIR}/${SQLBASENAME}_tkprof.out"
fi
# Login to SQL*Plus and toggle trace and run SQL...
runsql()
{
sqlplus $ORACLUSER/$ORAPASSWD <<EndSQL
alter session set sql_trace = true;
@$SQLSCRIPT
alter session set sql_trace = false;
exit;
EndSQL
} >/dev/null
# Get the last trace file and run tkprof on it...
runtkprof()
{
TKPROFIN=`ls -1t $UDUMPDIR/*.trc|head -n 1`
tkprof $TKPROFIN $TKPROFOUT EXPLAIN=$ORACLUSER/$ORAPASSWD
}
runsql
runtkprof
echo "Created file $TKPROFOUT"
--------------------------------------------------------------
-----Original Message-----
Sent: Tuesday, July 24, 2001 3:51 PM
To: Multiple recipients of list ORACLE-L
One idea would be to start SQL Trace for your session, execute the stored
procedure, stop SQL Trace. That will create a trace file in your udump
directory which you can format using TKPROF to include the explain plan for
each SQL statement executed. E.g.,
tkprof xxxxx.trc formattedfile.txt sys=no explain=username/password
Jay Miller
-----Original Message-----
Sent: Tuesday, July 24, 2001 3:06 PM
To: Multiple recipients of list ORACLE-L
Hi,
Can anyone show me how to run explain plan on a whole
stored procedure? 815 on Sun 5.6. Thanks a lot.
Leslie
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Orr, Steve
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
