Re: TRACE made easy - Source Code

I have been using the following script, being somewhat reluctant to learn to use the sql editor. The script deletes the query plan from the PLAN_TABLE immediately after displaying it.

Place your (single) statement in a file and call like "explain test.sql".

Caveat: the statement must start at the first line of your sql-file. I think I haven't used this on anything else but SELECT -statements. And of course, use entirely at your own risk.

Regards,

Tapio

• cut here ----
  #!/bin/sh
  #
  # $Id: explain,v 1.3 1993/04/12 13:10:40 vtl Exp $
  #
  

defaultuser=/
pagesize="set pagesize 0;
"

case $# in
 1|2|3|4) ;;
 *) echo "
Explain the Oracle query plan for a file containing one 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

file=$1

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

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

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

cat $file >> $tmp

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

sqlplus -s $user < $tmp 2> /dev/null |
  egrep -v 'Explained|deleted|Commit|^ *$'

• cut here again ... ----

Here is some example output:

toimenpiteet: cat testi.sql
SELECT * FROM kytkentapisteet WHERE
  kytp_avain IN
   (SELECT kytk_kytp2_avain FROM kytkennat     WHERE kytk_kytp1_avain IN
  (SELECT paa_kytp_avain FROM yhteys_paat WHERE     paa_yht_avain = 4697) );
toimenpiteet: explain testi.sql 4

    nested loops

        view
            sort JOIN
                nested loops
                    table access BY ROWID yhteys_paat
                        index RANGE SCAN paa_kuuluu2_frgn
                    table access BY ROWID kytkennat
                        index RANGE SCAN kytk_kytkee_frgn
        table access BY ROWID kytkentapisteet
            index UNIQUE SCAN kytp_pk_prim

--
! tapio luukkanen  !  vtl_at_tik.vtt.fi (130.188.52.2)
! home (358-0) 455 1067  !  work 456 6056