Re: Problems during explain plan

From: Tapio Luukkanen <vtl_at_hemuli.tte.vtt.fi>
Date: 1997/02/14
Message-ID: <walo8rjzkr.fsf@morko.tte.vtt.fi>#1/1

Caveat: the following works only on Unix.

We use a common plan_table, and the following Unix Korn shell script, which I call 'explain'.

The SQL statement to be analyzed should be written into a file, and processed like: 'explain file.sql'

The trick here is to create a temporary SQL-file which contains the 'EXPLAIN PLAN FOR your_obscure_statement' -statement, a 'select' on the plan_table, and a 'delete' for the relevant rows of the plan table immediately after the 'select'. Every 'explain' also gets a unique statement id, so no problems with concurrent users.

Note that the file should contain only one statement, and it should begin on the first line of the file.

I'm not sure if this works on Oracle 7; Oracle6 should be OK.

Hope this helps you.

  Tapio Luukkanen

---

#!/bin/ksh
#
# $Id: explain,v 1.9 1997/02/14 12:58:42 vtl Exp vtl $
#

conf=/usr/local/kassu/runtime/bin/change_sid test -r $conf && . $conf

PAGESIZE="set pagesize 0;
"

case $# in
 1|2|3|4) ;;
 *) echo "
Explain the Oracle query plan for a file containing a sql-statement. The PLAN_TABLE can be created with $ORACLE_HOME/rdbms/admin/xplainpl.sql. usage: explain sqlfile [user/pw] [indent [showheader]]] "; exit 1 ;;
esac

tmp=`mktemp`

trap "rm $tmp 2> /dev/null" 0 1 2 3 15

catter=cat

case "$1" in
  -) file="" ;; # read stdin
  --) file=""; catter='sed -n "/^SELECT/,/; *\$/p"' ;;   *) file=$1

     test -f $file || file=$1.sql
     test -f $file || file=$1.SQL
     test -f $file || { echo $0: no such file $1; exit 1; }
     ;;

esac

case "$2" in */*) user="$2"; shift ;; *) user=/ ;; esac indent=${2:-2}
id=$$
[ -n "$3" ] && PAGESIZE=""

smso=`tput smso`
rmso=`tput rmso`

SQLPLUS="sqlplus -s $user"

echo "EXPLAIN PLAN
  SET STATEMENT_ID = '$id'
  INTO PLAN_TABLE
  FOR" > $tmp

eval $catter $file >> $tmp

# To recieve a properly sorted output the following index should
# be created:
# CREATE INDEX plan_i ON plan_table(statement_id, parent_id, id);

echo "

${PAGESIZE}select LPAD(' ',$indent*LEVEL)||lower(OPERATION)||' '|| OPTIONS||' '||lower(OBJECT_NAME)

    QUERY_PLAN
FROM PLAN_TABLE
WHERE STATEMENT_ID = '$id'
CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = '$id' START WITH ID = 1; delete from plan_table where statement_id = '$id'; commit;
" >> $tmp

cat $tmp | $SQLPLUS 2> /dev/null |
  egrep -v 'Explained|deleted|Commit|^ *$' |   sed -e '/FULL/s/$/ <====/' -e '/FULL/s/^ /====> /g'

    # 's:FULL:F _U _L _L _:g'      Received on Fri Feb 14 1997 - 00:00:00 CST