Trace Analyzer (TRCANLZR) Oracle's Utility

Trace Analyzer is a lesser known utility that analyzes Oracle trace (.trc) files. It is the most comprehensive tracing tool available and has many advantages over other methods including TKPROF. Trace Analyzer was originally created to assist debugging of Oracle Applications although the benefit of the tool is application independent.It Analyzes Raw SQL Trace generated by EVENT 10046 any Level (1, 4, 8 or 12).

How Oracle trace analyzer Works
Trace Analyzer requires that a one-time configuration be performed. During this configuration, many objects are installed in the database to serve as a tracing repository. Once downloaded from Metalink and installed, a SQL script can be executed passing in the name of the trace file.

Executing Oracle Trace Analyzer
First we need to enable the trace,

a) Session Level Trace 
 alter session set tracefile_identifier='10046';
 alter session set timed_statistics = true;
 alter session set statistics_level=all;
 alter session set max_dump_file_size = unlimited;
 alter session set events '10046 trace name context forever,level 12';

b) Tracing Ongoing process
  connect / as sysdba
 oradebug setospid 14901
 oradebug unlimit
 oradebug event 10046 trace name context forever,level 12
Detailed information can be obtained on 10046 trace from following metalinik note"Recommended Method for Obtaining 10046 trace for Tuning [ID 376442.1]"

After the trace file is generated

SQL> START TRCANLZR.sql udump prod_ora_14901.trc;

You can reffer to metalink document "Trace Analyzer TRCANLZR - Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046 [ID 224270.1]" to configure Trace Analyzer on your environment.

Trace Analyzer provieds much more detailed information than tkprof, Trace Analyzer produces a comprehensive report out of it including WAITs and BINDs.Besides summary of CALLs and WAITs, it XRF WAITs related to datafiles access, back to schema objects, showing actual tables and indexes with corresponding WAITs, including number of blocks accessed.

The main disadvantage is, It needs to be configured unlike the tkprof.
~~~~~~Cheers