Database Reference
In-Depth Information
DeBUGGING eVeNtS
a debugging event, which is identified by a numeric value, is the means used to set a type of flag in a running
database engine process. the aim is to change its behavior, for example, by enabling or disabling a feature, by
testing or simulating a corruption or crash, or by collecting trace or debug information. some debugging events
aren't simple flags and can be enabled at several levels. each level has its own behavior. in some situations, the
level is an address of a block or memory structure.
you should use a debugging event with care and set it only when directed to do so by oracle support or if you
know and understand what the debugging event is going to change. debugging events enable specific code
paths. therefore, if a problem occurs when a debugging event is set, it's worth checking whether the same
problem can be reproduced without the debugging event set.
few debugging events are documented by oracle. if documentation exists, it's usually provided through oracle
support notes. in other words, debugging events are generally not described in the official oracle documentation
about the database engine. you can find a complete list of the available debugging events in the file
$oraCle_home/rdbms/mesg/oraus.msg. note that this file isn't distributed on all platforms. the range
from 10,000 to 10,999 is reserved for debugging events.
Enabling SQL Trace with ALTER SESSION
The
ALTER SESSION
statement, as described in the
SQL Language Reference
manual, can be used to enable SQL trace.
Here's an example:
ALTER SESSION SET sql_trace = TRUE
Your only option using the
ALTER SESSION
statement is to set
sql_trace
to
TRUE
, which yields a level 1 trace.
In practice, a level 1 trace is usually not enough. In most situations you need to break up the response time completely
to understand where the bottleneck is. For this reason, I won't describe this method of enabling SQL trace further.
Instead, I cover how to enable SQL trace at any level using the method described in Oracle Support note
EVENT: 10046 “enable SQL statement tracing (including binds/waits)”
(21154.1). To enable and disable SQL trace at
any level, you set the
events
initialization parameter by executing the
ALTER SESSION
statement. The following SQL
statement enables SQL trace at level 12 for the session executing it. Notice how the event number and the level are
specified:
ALTER SESSION SET events '10046 trace name context forever, level 12'
The following SQL statement disables SQL trace for the session executing it. Notice that this is
not
achieved by
specifying level 0:
ALTER SESSION SET events '10046 trace name context off'
You can also set the
events
initialization parameter by executing the
ALTER SYSTEM
statement. The syntax is the
same as for the
ALTER SESSION
statement. In any case, not only is there usually no point in enabling SQL trace at the
system level, but, in addition, the overhead of doing so can be huge. Also note that it takes effect only for sessions
created after it's executed.
