|
|
 |
Donald K. Burleson
Oracle Utilities Tips |
The Oracle Trace Utility
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.
 |
For more details on Oracle utilities, see
the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve
Callan.
You can buy it direct from the publisher for
30% off directly from
Rampant TechPress.
|
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
|
