Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle


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:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.