RE: how to run explain plan on SP???

From: Orr, Steve <sorr_at_rightnow.com>
Date: Tue, 24 Jul 2001 14:10:48 -0700
Message-ID: <F001.00353D71.20010724141530@fatcity.com>

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: sorr_at_rightnow.com

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: ListGuru_at_fatcity.com (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).

Received on Tue Jul 24 2001 - 16:10:48 CDT