Database Reference
In-Depth Information
Enabling SQL Trace with DBMS_MONITOR
Oracle Database also provides the
dbms_monitor
package, which you can use to enable and disable SQL trace. Not
only does the package provide a way of enabling extended SQL trace at the session level, but, more importantly,
you can enable and disable SQL trace based on the session attributes (see the “Database Calls” section in Chapter 2).
These attributes include: client identifier, service name, module name, and action name. This means that if the
application is correctly instrumented, you can enable and disable SQL trace independently of the session used to
execute the database calls. Nowadays, this is particularly useful because in many situations connection pooling is
used, so users aren't tied to a specific session.
When using the
dbms_monitor
package, you don't directly specify the levels of debugging event 10046. Instead,
each procedure that enables SQL trace provides three parameters (
binds
,
waits
, and, as of version 11.1,
plan_stat
)
that allow enabling a specific level. Use these parameters as follows:
binds
parameter has to be set to
TRUE
.
•
To enable level 4, the
waits
parameter has to be set to
TRUE
.
•
To enable level 8, the
plan_stat
parameter has to be set to
all_executions
.
•
To enable level 16, the
plan_stat
parameter has to be set to
never
.
•
To enable level 32, the
dbms_monitor
.
The
waits
parameter defaults to
TRUE
. The
binds
parameter defaults to
FALSE
. The
plan_stat
parameter defaults
to
NULL
(equivalent to
first_execution
). Therefore, the default level is 8.
The following sections illustrate some examples of using the
dbms_monitor
package for enabling and disabling
SQL trace at the session, client, component, and database levels. Note that, by default, only the users with the
dba
role
enabled are allowed to execute the procedures provided by the
dbms_monitor
package.
•
It's not possible to enable level 64 through
Session Level
To enable and disable SQL trace for a session, the
dbms_monitor
package provides the
session_trace_enable
and
session_trace_disable
procedures, respectively.
The following PL/SQL call enables SQL trace at level 8 for the session identified by ID 127 and serial number 29:
dbms_monitor.session_trace_enable(session_id => 127,
serial_num => 29,
waits => TRUE,
binds => FALSE,
plan_stat => 'first_execution')
All parameters have default values. If the two parameters identifying the session aren't specified, SQL trace is
enabled for the session executing the PL/SQL call.
When SQL trace has been enabled with the
session_trace_enable
procedure, the
sql_trace
,
sql_trace_waits
,
and
sql_trace_binds
columns of the
v$session
view are set accordingly. In addition, as of version 11.1, the
sql_
trace_plan_stats
column is also available. Warning: this happens only when the
session_trace_enable
procedure
is used and at least one SQL statement has been executed by the session to be traced. For example, enabling SQL trace
with the previous PL/SQL call will result in the following information being given:
SQL> SELECT sql_trace, sql_trace_waits, sql_trace_binds, sql_trace_plan_stats
2 FROM v$session
3 WHERE sid = 127;
