Database Reference
In-Depth Information
CHAPTER 6
■ ■ ■
Event 10046 and Extended
SQL Trace
T
he
Oracle Database Performance Tuning Guide 10g Release 2
states that event 10046 at level
8 may be used to enable logging of wait events to a SQL trace file. Additional event levels are
undocumented. Event 10046 is ideal for enabling extended SQL trace. When combined with
ALTER
SESSION
, event 10046 is the only way to enable extended SQL trace that does not require
DBA or SYSDBA privileges. This event is useful for building a self-tracing capability into an
application. Self-tracing means the ability of an application to create evidence of a perfor-
mance problem upon request by the end user. Whenever an end user is dissatisfied with the
performance of an application he or she may switch on extended SQL trace without assistance
from a DBA. Such a feature is a big time-saver since the DBA does not need to identify which
database session serves the end user and it also reduces the risk that a transient performance
problem does not reproduce by the time a DBA can attend to it.
Event 10046 is used in the context of performance diagnosis in several places in this topic.
Part 8 contains numerous examples of leveraging event 10046. This chapter is intended as a
brief reference for the event and its levels. The event is most useful at session and process level.
Examples of using the event at session level are in Chapter 13. Please refer to Chapter 37 for
instances of using the event at process level.
The supported event levels are detailed in Table 6-1. The term
database call
refers to the
parse, execute, and fetch stages of executing SQL statements.
Table 6-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
The following example illustrates how to use event 10046 to trace SQL statements, bind
variables, and wait events. The trace file is from Oracle11
g
Release 1. Note the new Oracle11
g
parameter
sqlid
, which corresponds to
V$SQL.SQL_ID
, in the
PARSING
IN
CURSOR
entry.
61
