Using Oracle Trace Analyzer (trcanlzr.sql)
Dave Moore: Author of Oracle
Utilities
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.
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.
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 Blocks
waited
on Waited Zero Time
Wait Waited Accessed
----------------------------------------- ---------
--------- ------- ------- --------
PL/SQL lock
timer........................ 15 0
5.01 75.08
log file
sync............................ 1 0
0.01 0.01
library cache
pin........................ 1 0
0.00 0.00
SQL*Net message from
client (idle)....... 2 0 17.22
30.21
SQL*Net message to
client (idle)......... 3 0 0.00
0.00
total.................................... 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.
