Trace Analyzer

DATABASE/Tuning 2011. 5. 16. 11:27

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:
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:


race Analyzer would also display:

0:"2/4/2003 15:57:35" 1:1

which equates to the actual SQL statement of:


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. 


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

...owner.index_name                  num rows     blocks     sample last analyzed date
------------------------------------ ---------- ---------- ---------- -------------------

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.

원문 :



'DATABASE > Tuning' 카테고리의 다른 글

Trace Analyzer  (0) 2011.05.16
Trackbacks 0 : Comments 0

Write a comment