Low-Level Trace Files

David Clement

September 2004

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 session events.

A session event is set by a command like the following.

alter session set events '10046 trace name context forever, level 12';
        
This turns on tracing with a defined event, 10046. The usual command to end tracing will work.
alter session set sql_trace=FALSE;
        
This works when you are logged into the session that you want to trace. You might consider adding these commands to source code for debugging.

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 PL/SQL block.

begin
    dbms_system.set_bool_param_in_session(sid=>&&sid,
        serial#=>&&serial,
        parnam=>'timed statistics',
        bval=>true);
    dbms_support.start_trace_in_session(sid=>&&sid,
        serial#=>&&serial,
        waits=>true,
        binds=>true);
end;
        
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.
begin 
    dbms_support.stop_trace_in_session (sid=>&&sid,
        serial#=>&&serial);
end;
        
Another possibility is to use dbms_system.set_ev to set the event. An example for the 10053 event is
begin
    dbms_system.set_ev (sid=>&&sid, serial#=>&&serial, 10053, 1, ??);
end;
        
To stop the event you could use the following.
begin
    dbms_system.set_ev (sid=>&&sid, serial#=>&&serial, 10053, 0, ??);
end;
        
These blocks should be saved as scripts for convenience.