Donald K. Burleson
Oracle Utilities Tips
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
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
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
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA