Table of contents
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
- TRCANLZR (TRCA): SQL_TRACE/Event 10046 Trace File Analyzer – Tool for Interpreting Raw SQL Traces [ID 224270.1]
- khailey / oracle_trace_parsing
- Oracle Session Resource Profiler
- TriVaDis eXtended Tracefile Analysis Tool
- SQL Trace Summary and Interpretation Tools
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 ???
Tom says:
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.
David Mann says:
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.
Yannick Jaquier says:
Thanks for comment David.
The ‘feature matrix’ sounds great idea, if you can club with an output example would help people to choose faster the one they have to use…
Thanks, Yannick.