Database Reference
In-Depth Information
calls (parse, execute, fetch) is not even granted to the role DBA. All three are too powerful to
allow normal database users to execute them. Last but not least, there is the SQL*Plus command
ORADEBUG
, which is merely available to SYS. So the average database user who is proficient in
performance diagnosis is still at a loss when it comes to creating trace files without bothering a
DBA—if it weren't for
ALTER
SESSION
SET
EVENTS
.
ALTER SESSION SET EVENTS
ALTER
SESSION
SET
EVENTS
is ideal for building self-tracing capability into applications and for
enabling SQL trace in a logon trigger. By self-tracing, I mean the ability of an application to
enable SQL trace depending on an environment variable or a menu item in a graphical user
interface. The syntax for switching an event on is as follows:
ALTER SESSION SET EVENTS 'event_number TRACE NAME CONTEXT [FOREVER,] LEVEL lvl'
The kind of event is determined by the integer
event_number
. The level, which often
controls the verbosity, is set with the integer
lvl
. By including the keyword
FOREVER
, the event
remains on, whereas without it, the event is only switched on momentarily. Normally, an event
must remain switched on for a longer period of time, hence
FOREVER
is almost always used. If,
for example, you were to execute
ALTER
SESSION
SET
EVENTS
'10046
trace
name
context
level
1'
,
then the resulting SQL trace file would record the
ALTER
SESSION
statement and tracing would
be switched off when it finishes. Not very useful. Instead, you will want to use
ALTER
SESSION
SET
EVENTS
'10046
trace
name
context
forever
,
level 12'
to trace wait events and bind variables of
all subsequent statements.
The syntax for switching events off is as follows:
ALTER SESSION SET EVENTS 'event_number trace name context off'
The usual approach is to first enable event 10046, then exercise a code path that requires
optimization, and finally to switch off event 10046. Performance diagnosis may then be done
with TKPROF or with an extended SQL trace profiler such as ESQLTRCPROF, which is included
in this topic (see Chapter 27). In case you suspect or already know that the optimizer picks
suboptimal execution plans for some of the traced statements, you should also enable event 10053
with the same syntax and for the same interval as event 10046. Event 10053 instructs the cost-
based optimizer to write a log of its decision-making process to a trace file. Level 1 is the correct
level for this event.
Using
ALTER
SESSION
SET
EVENTS
, it's also possible to obtain all of the name-based dumps,
such as system state, library cache, heap, control file, and many more. The SQL*Plus command
ORADEBUG
DUMPLIST
prints a list of all available dumps. A while ago, Pete Finnigan pointed out
that a library cache dump may be used to glean passwords, which were used in
ALTER
USER
statements against Oracle8. This issue is fixed in Oracle10
g
, as the following test proves:
SQL> ALTER USER hr IDENTIFIED BY secret;
User altered.
SQL> ALTER SESSION SET EVENTS 'immediate trace name library_cache level 10';
Session altered.
The resulting trace file contains asterisks instead of the password:
