Databases Reference
In-Depth Information
CHAPTER 13
■ ■ ■
ALTER SESSION/SYSTEM
SET EVENTS
A
LTER
SESSION
SET
EVENTS
and
ALTER
SYSTEM
SET
EVENTS
are undocumented in the Oracle9
i
and
Oracle10
g
Oracle Database SQL Reference
manuals as well as in
Oracle Database SQL Language
Reference 11g Release 1
. The manual
Oracle Database Performance Tuning Guide 10g Release 2
contains an
ALTER
SESSION
statement for the presumably best known event 10046 at level 8. Yet,
it is undocumented how to switch event 10046 off. The remaining levels of event 10046, which
are also very useful, are undocumented too. Furthermore, there are hundreds of other undoc-
umented events that may be set with
ALTER
SESSION
and
ALTER
SYSTEM
, some of which may be
very useful too.
Still today, for a database user who has the privilege
ALTER
SESSION
but was not granted the
role DBA,
ALTER
SESSION
SET
EVENTS
is the only way to enable extended SQL trace in his own
session in such a way that wait events and/or bind variables are included in the SQL trace file.
Both
ALTER
SESSION
SET
EVENTS
and
ALTER
SYSTEM
SET
EVENTS
may also be used to request diag-
nostic dumps when a certain ORA-
nnnnn
error occurs.
Tracing Your Own Session
Supposedly every DBA and developer is aware of the SQL statement
ALTER
SESSION
SET
SQL_
TRACE=TRUE
. This statement creates a trace file, which logs all the SQL statements of the session
that executed the command. Actually it logs much more than that, e.g., session identification
information, application instrumentation entries, and timestamps, to mention a few. For details,
please refer to Chapter 24. What is lacking are wait events and information on bind variables.
1
Both are needed for reliable performance diagnoses and the reproduction of performance
problems.
Oracle10
g
was the first release that offered a documented interface to enable tracing of
bind variables. This is possible with the package
DBMS_MONITOR
, which also supports tracing
of wait events. But execute permission on
DBMS_MONITOR
is solely granted to the role DBA (see
$ORACLE_HOME/rdbms/admin/dbmsmntr.sql
). The same applies to the undocumented package
DBMS_
SYSTEM
, which is capable of setting events such as 10046. The undocumented package
DBMS_
SUPPORT
, which also provides tracing of wait events and bind variables in addition to database
1.
Wait events and bind variables are equally missing when SQL trace is enabled with the packaged
procedure
DBMS_SESSION.SET_SQL_TRACE(TRUE)
.
129
