Oracle Trace Analyzer (trcanlzr.sql)

This is a rare opportunity to get a senior Oracle Corporate Developer enhancing our utilities, so I encourage al of you to contact him with comments. Oracle Corporation is interested in feedback from Oracle Tips! Carlos Sierra, developer of the wonderful sqltxplain.sql (see MOSC) and trcanlzr.sql script writes:

Below is another superbly-improved Oracle Utility by Carlos, the new Oracle Trace Analyzer:

Using the Trace Analyzer Utility

Trace Analyzer (trcanlzr) is an application from Oracle with much the same purpose as TKPROF. It is also designed to help analyze the trace files generated by SQL tracing. Trace Analyzer offers enhancements over TKPROF in a number of areas. Several of the key improvements are as follows:

n  Trace Analyzer provides a more detailed list of wait events for every SQL statement that is part of the trace file. Only in recent versions has TKPROF provided at least limited wait information.  Older versions provide no information on wait events regardless of the trace data.

n  Trace Analyzer reports totals for statements that execute multiple times; whereas TKPROF  would report each execution separately.  This is important when tracing a process that is updating many records, but doing it one row at a time.  Identifying this with TKPROF requires more manual effort.

n  Trace Analyzer provides the values used by bind variables, as long as the trace file was generated at a level that includes bind variables; whereas this feature is not available with TKPROF.

Installation of Trace Analyzer is fairly straightforward as long as the instructions are followed completely.  It is very similar to installing Statspack . MOSC document 224270.1 provides an adequate explanation for finding the files to accomplish the installation as well as how to install and use it.  Be very careful to follow the instructions exactly.

Executing Trace Analyzer

The first step is to enable tracing 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 in the current directory.  It is possible to change the output spool file by modifying the trcanlzr.sql script.

.