Trace Analyzer
Click here for the paper I presented at IOUG in 2006 on Trace Analyzer.
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. This utility supports only version 8.1.6 and above due the requirement of being able to read OS files from PL/SQL into the database.
How it 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. Trace Analyzer will then read the trace file and provide useful statistical information. The trace file used by Trace Analyzer is the same .trc file generated by any session trace.
On installation, Trace Analyzer creates the following SQL files. These can be installed locally on a client PC or on the database server itself.
TRCACREA.sql - creates all objects needed by Trace Analyzer by calling other scripts below.
TRCADROP.sql - drops the schema objects.
TRCAPKGB.sql - creates the package body.
TRCAPKGS.sql - creates the package header (specification).
TRCAREPO.sql - creates the staging repository.
TRCADIRA.sql - creates the directory object pointing to the place where the trace files exist (only if placing traces on a directory other than user_dump_dest).
TRCAGRNT.sql - grants privileges needed to use Trace Analyzer
TRCAREVK.sql - revokes privileges granted by TRCAGRNT.
TRCAPURG.sql - purges old SQL traces from the repository.
TRCATRNC.sql - truncates the staging repository.
TRCANLZR.sql - main Trace ANalyzer script that generates the report.
TRCACRSR.sql - generates report for one cursor.
TRCAEXEC.sql - generates report for one cursor execution.
Executing 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.
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 = :b2
Trace Analyzer would also display:
0:"2/4/2003 15:57:35" 1: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.
DELETE FROM SCOTT.EMPLOYEE
call count cpu elapsed disk query current rows misses
------- ------- ------- -------- -------- --------- --------- ------------ ---------
Parse 1 0.00 0.00 0 0 0 0 0
Execute 3 0.05 0.52 0 27 224 216 0
------- -------- -------- -------- -------- --------- --------- ----------- --------
total 4 0.05 0.52 0 27 224 216 0
Explain Plan
---------------------------------------------------------------
...3 DELETE STATEMENT
...2 .DELETE OF 'SCOTT.EMPLOYEE
...1 ..TABLE ACCESS (FULL) OF ‘SCOTT.EMPLOYEE'
OWNER.TABLE_NAME
...owner.index_name num rows blocks sample last analyzed date
------------------------------------ ---------- ---------- ---------- -------------------
SCOTT.EMPLOYEE..........................
The output above indicates that the EMPLOYEE table does not have statistics.
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.
The benefits of the Trace Analyzer far outweigh the negatives since never before has so much useful information been available to the Oracle tuner in one place. Usually, tkprof needs combined with statspack reports and other utilities to provide a comprehensive picture. Trace Analyzer takes tuning to a new level.