SQL Trace Summary and Interpretation Tools

Submitted by dave on Tue, 04/06/2010 - 15:25

Profiler Tools

TKProf
Old Standby, supplied by Oracle, installed with each RDBMS by default. 11.2 Documentation

TRCANLZR
Metalink Doc 224270.1 Oracle supplied utility that creates HTML report from a trace file.

OraSRP by OracleDBA.ru
Session Resource Profiler - A popular trace file analysis tool. Produces HTML reports.

DBA Solutions - shell script
10046 Trace Analysis Script provided by DBA Solutions, Inc. - A shell/awk script that reportedly can consume large trace files. Updated 31-MAY-2012.

TVD$XTAT
TVD$XTAT by Christian Antognini, author of Troubleshooting Oracle Performance. Some mention of it in that book and blog posts about it available here: here. Beta 9 version last updated in April 2009.

Clive Bostock perl script
https://sites.google.com/site/oraclemonitor/10046-pl - Perl script by Clive Bostock that analyzes WAIT lines from a Trace file.

Kyle Hailey Perl Script
GitHub source code and a blog post here.

SQL Developer
SQL Developer can open and browse trace files. My experience is that the feature is currently slow and limited. If you like what they have and want developers to pay more to it, give the feature some love on SQL Developer Connection site.

MERITS Profiler by Norbert Debes
MERITS - $ - Rewritten in Java for 11.2. Basic functionality for free, advanced functionality for license fee. Paid version includes interesting real-time trace file reading feature.

Fourth Elephant - Digger
Digger - $- Extension for SQL Developer. Some basic functionality for free and advanced functionality for a fee.

Method-R Profiler
Method-R Profiler - $ - Highly regarded trace file analysis tool.

Trace File Utilities

MR Trace
MR Trace - $ - Method R plug-in for SQL Developer - enables grabbing Trace Files from a server to a SQL Developer session

Techniques to Access Trace Files from within Oracle
If you want to roll your own analysis you might want to check out these techniques:
Code from Dion Cho - Quick demo on how to grab trace file contents via UTL_FILE

Jeremy Schneider's - discussion and code to scan UDUMP/BDUMP for trace files and provide summary about all trace files found as a view
profiling trace files with preprocessor external tables in 11g by Adrian Billington - Technique to leverage external tables and use a preprocessor script to feed the contents of the external table.

Other Helpful Tuning Tools
Oracle Execution Trace - Oracle Execution Trace is a small, easy to use application written in Java designed to help you trace the execution plan for a given database query. This tool works with Oracle 10g or higher. Written by Markus Geiß.

Oracle SQLTXplain

10053 Viewer by Hans-Peter Sloot

Retired Tools / Not Under Active Development
Simple Profiler - Niall Litchfield's HTMLDB "enables you to build a response time profile from an Extended Trace file generated for a single Oracle session"