Monday, September 16, 2013

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 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)


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 This is its output:

$ ls
sqls  trcanlzr.sql
$ 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 file trcanlzr_45318.log for parsing messages and totals.

TRCANLZR completed.
analyze file with anlzr end.

$ ls
1.txt   trcanlzr_45318.html  trcanlzr_45318.txt
sqls   trcanlzr.sql  trcanlzr_45318.log   trcanlzr_error.log 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

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

read -p "please input sql file name:" SQLFILENAME
if [ -z ${SQLFILENAME} ]; then
  echo "Miss Sql file name input."
  exit -1

if [ ! -e ${SQLFILENAME} ]; then
  echo "$SQLFILENAME does not exist."
  exit -1

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

if [ ${FLUSH_FLAG} = "Y" ]; then
  sqlplus -S / as sysdba <<EOF
  alter system flush buffer_cache;
  alter system flush shared_pool;
  if [ $? -eq 0 ]; then
    echo "buffer_cache and shared_pool flushed."
    echo "Error on flushing."
    exit -1

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;
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;


while read TRACE_FILE_NAME
done < 1.txt

if [ $? -eq 0 ]; then
  echo "Created a trace file $TRACE_FILE_NAME."
  echo "failed to trace $SQLFILENAM."
  exit -1

#cd "${TRACE_DIR}"
if [ -f "trcanlzr.sql" ]; then
  sqlplus -S / as sysdba <<EOF
  @trcanlzr.sql ${TRACE_FILE_NAME};
  if [ $? -eq 0 ]; then
    echo "analyze file with anlzr end."
    echo "error on analyzing file with anlzr end."
    exit -1
 echo "$trcanlzr.sql doesn exist!"

No comments:

Post a Comment