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

Tracing Utilities

This chapter will discuss those utilities that aid a DBA in tracing database activity. Tracing is an activity that is critical to a DBA in order to accurately detect and diagnose all sorts of database issues. The utilities covered in this chapter include tkprof, autotrace, Oracle Trace, oradebug and Trace Analyzer.

The first utility described in this chapter is one of the most underutilized utilities within Oracle – tkprof.

Formatting Trace Files with tkprof

tkprof is one of the most useful utilities available to DBAs for diagnosing performance issues. It essentially formats a trace file into a more readable format for performance analysis. The DBA can then identify and resolve performance issues such as poor SQL, indexing, and wait events.

tkprof has been historically difficult to use for many reasons. First, the entire process of enabling tracing, finding trace files, and executing the utility against them is a burdensome task. Once the DBA finally has the trace file output, the typical response is “Now what do I do”? Second, even though tkprof formats the data, it lacks any additional insight needed to remedy the problems revealed. In fact, problems are not even highlighted, thereby putting more work on the DBA to analyze the output, assess the problems, and determine what to do.

Why, When tkprof?

The DBA will use tkprof and session tracing when the database or a particular session is having performance problems. tkprof will generally be used infrequently when researching a very particular performance issue. A user may complain that the response time for a session is abysmal compared to the prior week. Session tracing and tkprof can be used to see exactly what is happening on the database, thus enabling the DBA to take corrective action.

The utility can also be used to view SQL that is being executed for an application. In some situations, this will be the only mechanism a DBA will have to view SQL. These situations include the execution of encrypted PL/SQL code on the database or submission of SQL statements from third party applications.

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.