Database Reference
In-Depth Information
CHAPTER 27
■ ■ ■
ESQLTRCPROF Extended
SQL Trace Profiler
O
ne might say that developing profiler tools for analyzing extended SQL trace files has been
en vogue for the last few years. TKPROF, Oracle's own profiler has seen only marginal improve-
ments since it first supported wait events in Oracle9
i
. Other tools offer a much more profound
analysis of trace files than TKPROF. First of all there is Oracle's free Trace Analyzer (see Metalink
note 224270.1). This PL/SQL-based tool creates a very detailed HTML report. The downside is
that it needs to be installed into a database, which may preclude an ad-hoc analysis of a production
system. Ideally it is run in the same instance that was traced, since it will then translate object
identifiers into the corresponding segment names.
Another freely available and useful tool is TVD$XTAT written by fellow Apress author
Christian Antognini. It is a Java program that also creates an HTML report. The ability to create
wait event histograms is one of its more advanced features. Unlike Trace Analyzer, it runs inde-
pendently of an Oracle DBMS instance and is much faster. It cannot translate database object
identifiers within wait events into segment names of tables and indexes.
ESQLTRCPROF is my own Perl-based profiler for Oracle9
i
, Oracle10
g
, and Oracle11
g
extended SQL trace files. ESQLTRCPROF is capable of parsing the undocumented extended
SQL trace file format. It calculates a resource profile for an entire SQL trace file and for each
cursor in the trace file. It appears to be the only profiler that supports think time as a separate
contributor to response time. It also breaks down elapsed time, CPU time, and wait time by
recursive call depth, thus pointing out at which call depth most of the response time is consumed.
Like the other profilers mentioned, ESQLTRCPROF is a replacement for TKPROF, since it
addresses several shortcomings of Oracle Corporation's own official SQL trace analysis tool.
351
