SQL Trace Summary and Interpretation Tools
Submitted by dmann on Tue, 04/06/2010 - 15:25
TKProfOld Standby, supplied by Oracle, installed with each RDBMS by default. 11.2 Documentation
TRCANLZRMetalink Doc 224270.1 Oracle supplied utility that creates HTML report from a trace file.
OraSRP by OracleDBA.ruSession Resource Profiler - A popular trace file analysis tool. Produces HTML reports.
DBA Solutions - shell script10046 Trace Analysis Script provided by DBA Solutions, Inc. - A shell/awk script that reportedly can consume large trace files. Updated 31-MAY-2012.
TVD$XTATTVD$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 scripthttps://sites.google.com/site/oraclemonitor/10046-pl - Perl script by Clive Bostock that analyzes WAIT lines from a Trace file.
Kyle Hailey Perl ScriptGitHub source code and a blog post here.
SQL DeveloperSQL 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 DebesMERITS - $ - 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 - DiggerDigger - $- Extension for SQL Developer. Some basic functionality for free and advanced functionality for a fee.
Method-R ProfilerMethod-R Profiler - $ - Highly regarded trace file analysis tool.
Trace File Utilities
MR TraceMR 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 OracleIf 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 ToolsOracle 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ß.
10053 Viewer by Hans-Peter Sloot