Low-Level Trace Files
Oracle versions 7-8
Session events in Oracle were originally meant for database engineers
to use in debugging the kernel. They were not meant to be exposed
to users. However, some of them have proved so useful in tracing
the performance of Oracle database applications that they have gained
acceptance in the Oracle world. Oracle Corp. has begun to provide
analysis scripts, although these do not seem comparable to mature
tools, as yet. Two of the more commonly used session events for
performance optimization are the level 10046 and the level 10053
A session event is set by a command like the following.
This turns on tracing with a defined event, 10046. The usual command
to end tracing will work.
alter session set events '10046 trace name context forever, level 12';
This works when you are logged into the session that you want to
trace. You might consider adding these commands to source code for
alter session set sql_trace=FALSE;
There are other ways to start and stop tracing. For example,
procedures in the
dbms_system package will trace sessions
other than the current one. A trace can be started by first looking in
V$SESSION for the session ID and serial number of the
process that you want to trace, and then executing the following
If you do this in SQL*Plus, you will be prompted for the two
variables &&sid and &&serial. The following block will stop the trace
for the session.
Another possibility is to use dbms_system.set_ev to set the event.
An example for the 10053 event is
To stop the event you could use the following.
dbms_system.set_ev (sid=>&&sid, serial#=>&&serial, 10053, 1, ??);
These blocks should be saved as scripts for convenience.
dbms_system.set_ev (sid=>&&sid, serial#=>&&serial, 10053, 0, ??);