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 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.
-
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.
Metalink 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
First,
tracing needs to be 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 in the current
directory.
It is possible to change the output spool
file by modifying the trcanlzr.sql script.
Using
the trcsess utility
When solving tuning problems,
session traces are very useful and offer vital
information. Traces are simple and
straightforward for dedicated server sessions,
but for shared server sessions, many processes
are involved. The trace pertaining to the user
session is scattered across different trace
files belonging to different processes. This
makes it difficult to get a complete picture of
the life cycle of a session.
The
trcsess
command-line utility consolidates trace
information from selected trace files based on
specified criteria. The criteria include session
id, client id, service name, action name and
module name.
This allows the compilation of multiple
trace files into a single output file.
The
syntax for the
trcsess utility is:
trcsess [output=output_file_name]
[session=session_Id]
[clientid=client_Id]
[service=service_name]
[action=action_name]
[module=module_name]
[trace_files]
Where:
-
output specifies the file
where the output is generated. When this
option is not specified, the standard output
is used for the output.
-
session consolidates the
trace information for the session specified.
The session ID is a combination of session
index and session serial number.
-
clientid consolidates the
trace information given client ID.
-
service consolidates the
trace information for the given service
name.
-
action consolidates the
trace information for the given action name.
-
module consolidates the
trace information for the given module name.
-
trace_files is a list of
all trace file names, separated by spaces,
in which trcsess will look for trace
information. The wild card character * can
be used to specify the trace file names. If
trace files are not specified, all the files
in the current directory are checked by
trcsess.
Activating trace on multiple
sessions means that trace information is spread
throughout many trace files. For this
reason, Oracle 10g introduced the trcsess
utility, allowing trace information from
multiple trace files to be identified and
consolidated into a single trace file. The
trcsess usage is listed below.
trcsess [output=<output
file name >] [session=<session ID>] [clientid=<clientid>]
[service=<service name>] [action=<action
name>] [module=<module name>] <trace file
names>
output=<output file name> output
destination default being standard output.
session=<session Id> session to be traced.
Session id is a combination of session Index
& session serial number e.g. 8.13.
clientid=<clientid> clientid to be traced.
service=<service name> service to be traced.
action=<action name> action to be traced.
module=<module name> module to be traced.
<trace_file_names> Space separated list of
trace files with wild card '*' supported.
Use Oracle’s trcsess
command-line utility to consolidate the
information from all the trace files into a
single output file.
Then run tkprof
against the consolidated trace file to
generate a report. It is recommended that one
experiments some with tkprof to become
familiarized with the options available in this
useful utility.
..\udump> tkprof hr_report.trc
output=hr_trc_report
SORT=(EXEELA, PRSELA,FCHELA)
