trca: TKPROF on steroids

The usual way to troubleshoot a problematic Oracle sql is:

1. Trace the sql
2. Analyze the trace file

Tkprof takes a raw trace file and outputs information that is more readable to human ideas -- more readable, but far from enough. Check this simple tkprof output:

Very hard for my eyes to work out the indent and outdent. I almost mustered up my courage to write a simple program to line up the steps, then I found out “tracer analyzer” or trca, which started as an Oracle internal tool and has been around for more than 10 years. 

Here is the output from trca for the same sql:

Compare with the plain tkprof output, trac offers these nice little visual assistances:

• It lines up the steps by putting fields into columns
• It uses “....+” to indicate indentations.
• It puts “predicates” and “filters” at the same line as the execution plan step. 

Besides these visual assistances, it offers much more information:

• Statistics on related tables, columns (including histograms)
• Index information

Perhaps more importantly, unlike tkprof, trca doesn’t require you to log on to the Oracle server machine. 

 

Now here comes the bitter part of trca: you have to first install it!

Install trca 

Download trca from https://sites.google.com/site/oracledbnote/traceanalyzer or from My Oracle Support (MOS, previously known as Metalink under note 224270.1).

Read instructions.txt and follow the steps:

1. Navigate to trca/install directory
2. Start SQL*Plus connecting INTERNAL (SYS) as SYSDBA
3. Execute script tacreate.sql and respond to values requested. You will be asked to provide:

• TRCANLZR password
• Optional Host String (TNS Alias)
• TRCANLZR default tablespace
• TRCANLZR temporary tablespace
• Type of object for large staging tables - Enter "Y" is you want large tables to be created as PERMANENT, or "N" if you prefer GLOBAL TEMPORARY (recommended)

e.g

cd trca/install

sqlplus /nolog

SQL> connect / as sysdba

SQL> start tacreate.sql

 Use trca 

There are 2 ways to use trca:

cd trca/run

sqlplus [apps user]/[apps pwd]

SQL> start trcanlzr.sql largesql.trc  <== your trace file

SQL> start trcanlzr.sql control_file.txt  <== your text file

There are some gotchas in using trca. Read Along.

 A utility script to trace and analyze

Since I have a local Oracle instance under my complete control, when I have the need to troubleshoot a problematic sql, I often do:

1. Flush caches to ensure a clean-slate
2. Set trace on
3. Run the sql
4. Set trace off
5. Analyze the trace file

To automate this process, I wrote a small script traceSql.sh. This is its output:

$ ls

sqls  traceSql.sh  trcanlzr.sql

$ sh traceSql.sh

Do you want to flush buffer_cache and shared_pool (default N)? [Y/N]Y

System altered.

System altered.

buffer_cache and shared_pool flushed.

please input sql file name:sqls/test.sql

Created a trace file orcl_ora_9144.trc.

Value passed to trcanlzr.sql:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TRACE_FILENAME: orcl_ora_9144.trc

...analyzing orcl_ora_9144.trc

Trace Analyzer executed successfully.

There are no fatal errors in trcanlzr_error.log file.

Review file trcanlzr_45318.log for parsing messages and totals.

...copying reports into local SQL*Plus client directory

...trcanlzr 45318 reports were copied from server to local SQL*Plus directory

...review file trcanlzr_45318.log for parsing messages and totals.

TRCANLZR completed.

analyze file with anlzr end.

$ ls

1.txt  traceSql.sh   trcanlzr_45318.html  trcanlzr_45318.txt

sqls   trcanlzr.sql  trcanlzr_45318.log   trcanlzr_error.log

traceSql.sh takes a sql file sqls/test.sql and analyzes it -- make sure the sqls in the sql file are ended with semicolon(;). After this run, 4 files trcanlzr_45318.* are created by trcanlzr.sql , they are copied by trcanlzr.sql from Oracle server to the local directory.

Attention! If you want to use this script on a shared Oracle instance, you should change this script, so it won’t:

• flush buffer_cache and shared_pool
• log on as sysdba

traceSql.sh

#!/bin/bash

function getFlushFlag(){

  read -p "Do you want to flush buffer_cache and shared_pool (default N)? [Y/N]" FLUSH_FLAG

  if [ -z ${FLUSH_FLAG} ]; then

    FLUSH_FLAG="N"

  fi

}

read -p "please input sql file name:" SQLFILENAME

if [ -z ${SQLFILENAME} ]; then

  echo "Miss Sql file name input."

  exit -1

fi

if [ ! -e ${SQLFILENAME} ]; then

  echo "$SQLFILENAME does not exist."

  exit -1

fi

getFlushFlag

while [ ${FLUSH_FLAG} != "Y" -a ${FLUSH_FLAG} != "N" ]

do

  getFlushFlag

done

if [ ${FLUSH_FLAG} = "Y" ]; then

  sqlplus -S / as sysdba <<EOF

  alter system flush buffer_cache;

  alter system flush shared_pool;

  EXIT;

EOF

  if [ $? -eq 0 ]; then

    echo "buffer_cache and shared_pool flushed."

  else

    echo "Error on flushing."

    exit -1

  fi

fi

res01=`sqlplus -S / as sysdba <<EOF

set heading off feedback off pagesize 0 verify off echo off numwidth 4

alter session set timed_statistics = true;

alter session set statistics_level=all;

alter session set events '10046 trace name context forever,level 8';

spool sqloutput.txt;

@"$SQLFILENAME";

spool off;

spool 1.txt;

select (select instance_name from v\\$instance) ||'_ora_'||

          (select spid||case when traceid is not null then '_'||traceid else null end

                from v\\$process where addr = (select paddr from v\\$session

                                            where sid = (select sid from v\\$mystat

                                                       where rownum = 1

                                                  )

                                        )

          ) || '.trc' tracefile

    from v\\$parameter where name = 'user_dump_dest';

spool off;

alter session set events '10046 trace name context off';

alter session set sql_trace=false;

EXIT;

EOF`

while read TRACE_FILE_NAME

do

  break

done < 1.txt

if [ $? -eq 0 ]; then

  echo "Created a trace file $TRACE_FILE_NAME."

else

  echo "failed to trace $SQLFILENAM."

  exit -1

fi

#cd "${TRACE_DIR}"

if [ -f "trcanlzr.sql" ]; then

  sqlplus -S / as sysdba <<EOF

  @trcanlzr.sql ${TRACE_FILE_NAME};

  exit;

EOF

  if [ $? -eq 0 ]; then

    echo "analyze file with anlzr end."

  else

    echo "error on analyzing file with anlzr end."

    exit -1

  fi

else

 echo "$trcanlzr.sql doesn exist!"

fi