Trace Analyzer vs OraSRP vs Oracle Trace parsing vs TriVaDis eXtended Tracefile Analysis

 

Preamble

Post aim as comparing few SQL Trace analyzer tools (10046 trace files), testing has been done on an Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production running on a HPUX 11iv2 Itanium server.

ALTER SESSION SET timed_statistics=TRUE;
 
ALTER SESSION SET statistics_level=ALL;
 
ALTER SESSION SET max_dump_file_size=unlimited;
 
ALTER SESSION SET optimizer_features_enable='9.2.0';
 
ALTER SESSION SET events '10046 trace name context forever, level 12';
 
UPDATE dsrdba.forecast f
SET VALUE=0
WHERE
0<(SELECT COUNT(*) FROM dwhdba.acustomer_ship_to b, dwhdba.afinished_good c,
   dsrdba.man_fore_entry m
   WHERE f.acustomer_ship_to__code=b.code
   AND f.finished_good__code=c.code
   AND b.region__code LIKE m.region__code
   AND b.sales_area__code LIKE m.sales_area__code
   AND b.ship_to_group__code LIKE m.ship_to_group__code
   AND c.product_group__code LIKE m.product_group__code
   AND c.product_division__code LIKE m.product_division__code
   AND c.product_pnl_family__code LIKE m.product_pnl_family__code
   AND c.commercial_product__code LIKE m.commercial_product__code
   AND m.month__code='M201204');

This generated in my case a dwhqa_ora_20751.trc trace file.

Oracle Trace Analyzer

Let’s start with the Oracle corporation tool, easy to install (one script to execute) then you connect with trcanlzr Oracle account and issue:

SQL> START trcanlzr.SQL dwhqa_ora_20751.trc
 
Parameter 1:
Trace Filename OR control_file.txt (required)
 
 
VALUE passed TO trcanlzr.SQL:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TRACE_FILENAME: dwhqa_ora_20751.trc
 
Analyzing dwhqa_ora_20751.trc
 
TO monitor progress, login AS TRCANLZR INTO another SESSION AND EXECUTE:
SQL> SELECT * FROM trca$_log_v;
 
... analyzing trace(s) ...
 
 
 
Trace Analyzer completed.
Review FIRST trcanlzr_error.LOG FILE FOR possible fatal errors.
Review next trca_e46032.LOG FOR parsing messages AND totals.
 
Copying now generated files INTO local DIRECTORY
 
 
TKPROF: RELEASE 10.2.0.4.0 - Production ON Wed Aug 8 09:38:39 2012
 
Copyright (c) 1982, 2007, Oracle.  ALL rights reserved.
 
 
  adding: trca_e46032.html (deflated 88%)
  adding: trca_e46032.LOG (deflated 83%)
  adding: trca_e46032.tkprof (deflated 76%)
  adding: trca_e46032.txt (deflated 84%)
  adding: trcanlzr_error.LOG (deflated 81%)
test OF trca_e46032.zip OK
deleting: trcanlzr_error.LOG
Archive:  trca_e46032.zip
  LENGTH     DATE   TIME    Name
 --------    ----   ----    ----
    95287  08-08-12 09:38   trca_e46032.html
    15551  08-08-12 09:38   trca_e46032.LOG
     7326  08-08-12 09:38   trca_e46032.tkprof
    48540  08-08-12 09:38   trca_e46032.txt
 --------                   -------
   166704                   4 files
 
FILE trca_e46032.zip has been created
 
TRCANLZR completed.

Result has nice look and feel. All is graphically well represented what I rate missing is top wait events and a bit more information on I/O information like histograms in below other tools. There are anyway interesting sections on objects with most I/O and objects that have potential hot blocks…

khailey / oracle_trace_parsing

A perl script, as my test server is an HPUX box I had to change the /bin/bach by /bin/ksh in script header then using it is as simple as:

server1{oradwh}# ./parsetrc.pl /ora_dwhqa/dump/dwhqa/dwhqa_ora_20751.trc > parsetrc_dwhqa_ora_20751.txt

Output is only in text mode but has the top wait event section and I/O histograms. The I/O throughput is also a nice piece of information…

Oracle Session Resource Profiler (OraSRP)

This is a Windows tool so you need to transfer the trace file on your desktop and then it is really straightforward:

orasrp.exe --google-charts dwhqa_ora_20751.trc orasrp_dwhqa_ora_20751.html

Result is really interesting, same as Oracle trace Parsing one but fully in html. It also brings an appealing Google Chart pie. The statement display is a bit less user friendly than the Oracle corporation tool.

TriVaDis eXtended Tracefile Analysis Tool

Tools is available in Unix and Windows mode, I have chosen the Windows execution ahs Java is mandatory:

tvdxtat.cmd -i dwhqa_ora_20751.trc -r 10 -t html.xsl -w yes -s no -o tvdxtat_dwhqa_ora_20751.html

Result is almost the same as Oracle Session Resource Profiler (OraSRP) without the pie chart

Conclusion

Well no bad tool in this non-exhaustive list. My personal preference goes to Oracle Session Resource Profiler (OraSRP) for its nice display and in complement the Oracle Corporation tool for added information…

Feel free to comment on your favorite tool…

References

This entry was posted in Oracle and tagged . Bookmark the permalink.

4 thoughts on “Trace Analyzer vs OraSRP vs Oracle Trace parsing vs TriVaDis eXtended Tracefile Analysis

  1. Thanks for including some output examples. I have been wanting to take a pass at all the tools I have listed in my page but haven’t been able to find the time. Maybe some time I will get around to it and would also like to get together a ‘feature matrix’… as you mentioned some important things seem to be missing from some of the tools.

  2. I like the SQL Trace Analyzer but it means an install but you do get more information. My 2 favorite tools are OraSRP and the Hotsos (Method-R) profiler. I like OraSRP because it is free and you don’t need to install it to get it to generate an output that is easy to read. The Method-R profiler is nice because it is supported and it has skew analysis and several other neat features but costs $$$. So yes, bugs can get fixed and new features get added but I am sure the OraSRP author would fix any bugs encountered as well.

  3. Guillaume Goulet-Vallières says:

    I’m using orasrp everyday for two things. Nothing to install on the DB side, just pull the trace and call the window executable.

    Lost of informations concentrating on top wait events.

    I love it, did I said it was free ???

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>