Using Oracle Trace Analyzer (trcanlzr.sql)

Dave Moore:  Author of Oracle Utilities

Oracle has provided another utility initially designed for performance tuning Oracle Applications.  Trace Analyzer is provided in the form of a PL/SQL package (TRCA$ ).  The Trace Analyzer utility is available via download on the Oracle Metalink web site.

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, tracing needs enabled at the appropriate level. For example, to provide maximum trace data, a Level 12 trace can be started for the current session:

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

After the session executes for enough time to gain needed data, the trcanlzr SQL script can be executed.  It requires the name of the directory object.  This object points to the physical operating system directory for the user_dump_dest.  The installation of the utility will automatically create the directory object required (named UDUMP).

SQL>@d:/trcanlzr.sql UDUMP asg920xr_ora_13033.trc

Once executed, the output will be displayed on the screen and a spool file is created.

Is Oracle trace Analyzer better than tkprof?

Traditionally, tkprof has been the best tracing diagnostics tool available.  That is, until the introduction of Trace Analyzer which is everything tkprof is and more.   However, as of version 10.2, the Trace Analyzer utility is still not shipped with the Oracle DBMS like tkprof.  

Given access to both utilities, Trace Analyzer has the following advantages:

1.   Trace Analyzer provides the actual values of the bind variables in SQL.  No longer are DBAs faced with wondering what the values were at runtime – Trace Analyzer provides them. 

For the following SQL statement listed in the output:

DELETE FROM HISTORY where ALERT_TIME <= :b1  AND INSTANCE_NUMBER = :b

Trace Analyzer would also display:

0:"2/4/2003 15:57:35" 1: 

which equates to the actual SQL statement of:

DELETE FROM HISTORY where ALERT_TIME <= :"2/4/2003 15:57:35" AND INSTANCE_NUMBER = 1

2.   Trace Analyzer provides the hottest blocks, optimizer statistics for indexes and tables and other information not available through tkprof.  The output below shows the SQL statement, the execution plan and statistics for each object in the SQL. 

3.   Trace Analyzer separates user recursive and internal recursive calls, unlike tkprof.

4.   Trace Analyzer provides more detailed wait event information, which can be very useful to those DBAs that prefer wait-based tuning methodologies.  This data is also very helpful when there is a significant gap between CPU and elapsed times.

Event                                      Times     Count      Max.     Total    Blockswaited on                                 Waited   Zero Time    Wait    Waited  Accessed----------------------------------------- --------- --------- -------   ------- --------PL/SQL lock timer........................     15         0      5.01     75.08log file sync............................      1         0      0.01      0.01library cache pin........................      1         0      0.00      0.00SQL*Net message from client (idle).......      2         0     17.22     30.21SQL*Net message to client (idle).........      3         0      0.00      0.00total....................................     22         0     17.22    105.30     0

One drawback concerning Trace Analyzer is that it requires objects in the database (the tracing repository) and that means more configuration work on each database that needs the utility installed.  This is unlike tkprof, which is an executable file always there, ready to serve.

 

 

from:http://www.rampant-books.com/art_moore_oracle_trace_analyzer_trcanlzr_sql.htm