Database Reference
In-Depth Information
How to obtain trace files at various levels of detail is the topic of Chapter 28. The trace levels
and the type of trace file entries they enable are summarized in Table 24-1.
Table 24-1.
SQL Trace Levels
SQL Trace Level
Database Calls
Bind Variable Values
Wait Events
1
yes
no
no
4
yes
yes
no
8
yes
no
yes
12
yes
yes
yes
Sometimes extended SQL trace files are referred to as raw SQL trace files. Both terms are
indeed synonymous. Since there is nothing particularly raw about the files—they are perfectly
human-readable—I have decided not to use the adjective raw and will stick with the term extended
SQL trace file or simply trace file for the sake of conciseness.
SQL and PL/SQL Statements
The term cursor is often used in conjunction with
SELECT
statements and the iterative fetching
of rows returned by queries. However, the ORACLE DBMS uses cursors to execute any SQL or
PL/SQL statement, not just
SELECT
statements. SQL and PL/SQL statements in a trace file are
identified by their cursor number. Cursor numbers for SQL statements sent by clients start at 1. The
cursor number is the figure behind the pound sign (
#
) in entries such as
PARSING
IN
CURSOR
#1
,
PARSE
#1
,
EXEC
#1
,
FETCH
#1
,
WAIT
#1
, and
STAT
#1
. These examples all refer to the same cursor
number 1. Each additional SQL statement run by the client receives another cursor number,
unless reuse of a cursor number is taking place after the cursor has been closed. Entries relating
to the same statement are interrelated through the cursor number.
Not all operations executed are assigned a proper cursor number. One notable exception
is the use of large objects (LOBs) through Oracle Call Interface (OCI). When working with LOBs,
you may see cursor number 0 or cursor numbers for which a parse call is missing, although
tracing was switched on right after connecting. This does not apply to the PL/SQL LOB inter-
face
DBMS_LOB
.
Cursor numbers may be reused within a single database session. When the client closes a
cursor, the DBMS writes
STAT
entries, which represent the execution plan, into the trace file. At
this stage, the cursor number can be reused for a different SQL statement. The SQL statement
text for a certain cursor is printed after the first
PARSING
IN
CURSOR
#
n
entry above any
EXEC
#
n
,
FETCH
#
n
,
WAIT
#
n
, or
STAT
#
n
entries with the same cursor number
n
.
Recursive Call Depth
Anyone who has worked with the TKPROF utility is presumably familiar with the concept of
recursive and internal SQL statements. SQL Statements sent by a database client are executed
