Trace Analyzer TRCANLZR

Trace Analyzer TRCANLZR - Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046 [ID 224270.1]

--------------------------------------------------------------------------------

Modified 03-NOV-2009 Type TROUBLESHOOTING Status PUBLISHED

In this Document
Purpose
Last Review Date
Instructions for the Reader
Troubleshooting Details
References



--------------------------------------------------------------------------------



Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.1
OSS Support Tools - Version: 9.2.0
Oracle Application Object Library - Version: 11.5.5 to 12.1


Purpose
This tool reads and parses one or several SQL traces generated by
SQL Trace or by EVENT 10046 any level (1, 4, 8 or 12) and produces
a comprehensive report with performance related details.

Output report includes all the details found on TKPROF, plus
additional information normally used for a process performance
analysis.

Report generated includes: response time components, db call
summaries; list of top SQL according to response time; SQL genealogy
with recursive dependencies; segment i/o wait summaries; hot blocks;
gaps of no tracing activity; transaction summary; non-default
initialization parameters; row source plans; explain plans; tables
and indexes referenced by explain plans; indexed columns; CBO
statistics for tables, indexes and their columns; bind variables used
by relevant SQL; time analysis for SQL itself and their recursive
SQL; list and details of relevant executions for top SQL.

Trace Analyzer connects to one database. For best report content
install and use this tool in the same system/database where the trace
was generated. If this is not possible, you can still use this tool
in a similar system (Dev or Test) but please be aware that the
content of some sections will not be accurate, for example: explain
plans, i/o wait summaries, init.ora params, etc. When using in a
different system, consider using scripts in tra/dict to copy selected
pieces of the data dictionary from the SOURCE system into the TARGET
system.Last Review Date
November 3, 2009
Instructions for the Reader
A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.
Troubleshooting Details
For 9i, 10g and 11g instances, download tool trca.zip. Read intructions.txt included into zip file.

For sample output reports, download trca_sample.zip.

For 8i instances, download and use trca_old.zip


References
NOTE:215187.1 - SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly
NOTE:243755.1 - Implementing and Using the PL/SQL Profiler
NOTE:39817.1 - Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output

Attachments



--------------------------------------------------------------------------------
Trace Analyzer for 9i, 10g and 11g (1,245.42 KB)
Trace Analyzer for 8i (58.04 KB)
TRCANLZR Output Sample (270.92 KB)


Related



--------------------------------------------------------------------------------
Products
--------------------------------------------------------------------------------

Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
Internal Applications > Internal Applications > Internal Applications > OSS Support Tools
Oracle E-Business Suite > Applications Technology > Application Object Library > Oracle Application Object Library
Keywords
--------------------------------------------------------------------------------
TRACE ANALYZER; PERFORMANCE; EXPLAIN PLAN; COE; COESCRIPTS; SQL TUNING