Database Reference
In-Depth Information
CHAPTER 24
■ ■ ■
Extended SQL Trace File
Format Reference
A
SQL trace file contains a precise log of the SQL statements parsed and executed as well as
the fetch calls made by a database client. This includes the CPU time and the elapsed (wall
clock) time consumed by parse, execute, and fetch operations. Optionally, a SQL trace file
includes information on wait events, which are indispensable for an accurate performance
diagnosis. Last but not least, actual values for bind variables may also be included, e.g., when
the PL/ SQL package
DBMS_MONITOR
is used to enable SQL trace.
The SQL trace file format is undocumented, even though the utilities TKPROF and TRCSESS
are based on the analysis of SQL trace files. Understanding the format of extended SQL trace
files is an essential skill for any DBA who is confronted with performance problems or trouble-
shooting tasks. Since formatting trace files with TKPROF obscures important information,
such as statement hash values, timestamps, recursive call depths, instance service name,
module, action, and SQL identifiers (
V$SQL.SQL_ID
),
1
it is often mandatory to read and under-
stand the trace files themselves.
Introduction to Extended SQL Trace Files
Extended SQL trace files are by and large a statement-by-statement account of SQL and PL/SQL
executed by a database client.
2
Entries found in such files fall into these four major categories:
•
Database calls (parse, execute, and fetch)
Wait events
Bind variable values
Miscellaneous (timestamps, instance service name, session, module, action, and client
identifier)
Database calls, session identification, and other details from category miscellaneous are
logged when tracing is enabled at the lowest level 1, e.g., with
ALTER
SESSION
SET
SQL_TRACE=TRUE
,
whereas recording of wait events and bind variable values may be enabled independently.
1.
TKPROF release 11.1.0.7 is the first release that includes SQL identifiers in the report.
2.
Background processes may be traced, too, but they are normally not responsible for performance
problems.
271
