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.
Corporation is interested in feedback from Oracle Tips! Carlos Sierra,
developer of the wonderful sqltxplain.sql (see MOSC) and trcanlzr.sql
I am very interested in
receiving feedback from your people on my scripts. That helps me keep the
scripts current and useful.
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:
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.
Trace Analyzer reports totals for statements
that execute multiple times; whereas TKPROF
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.
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
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