Oracle Internals
JulianDyke.com

Welcome

Consultancy

Seminars

Blog

Presentations

Diagnostics

Internals

 

Trace

Enabling Trace

There are several ways to enable trace

Enabling Trace at Instance Level
Enabling Trace at Session Level
Enabling Trace in another Session
Enabling Trace in Oracle5
Enabling Trace in Oracle6

Enabling Trace at Instance Level

Trace can be enabled when the instance is started by adding the following line to the init.ora file

    sql_trace = TRUE

This will enable trace for all sessions including background processes. Note that enabling this parameter may generate large amounts of trace and consume significant system resources

Trace can also be enabled at instance level using event 10046. For example to enable event 10046 level 8 add the following line to the init.ora file

    event="10046 trace name context forever, level 8"

When the instance is already running, trace can be enabled directly using the ALTER SYSTEM command.

    ALTER SYSTEM SET trace_enabled = TRUE;

This will enable trace for all newly created sessions. Currently executing sessions and background processes will be unaffected

Instance-wide trace can be disabled again using

    ALTER SYSTEM SET trace_enabled = FALSE;

Trace can also be enabled using event 10046. For example to enable event 10046 level 8 use the command

    ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 8';

Instance-wide trace can be disabled again using

    ALTER SYSTEM SET EVENTS '10046 trace name context off';

Enabling Trace at Session Level

Trace can be enabled at session level using the command

    ALTER SESSION SET sql_trace = TRUE;

Trace is disabled at session level using

    ALTER SESSION SET sql_trace = FALSE;

Trace can also be enabled at session level using event 10046 For example to enable event 10046 level 8 use the command

    ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

Event 10046 trace is disabled at session level using

    ALTER SESSION SET EVENTS '10046 trace name context off';

Trace can be enabled in the current session using the DBMS_SESSION package. This can be useful if you need to enable trace from within a PL/SQL package.

Trace is enabled at session level using

    EXECUTE dbms_session.set_sql_trace (TRUE);

Trace is disabled at session level using

    EXECUTE dbms_session.set_sql_trace (FALSE);

Trace can be enabled in the current session using the DBMS_SUPPORT package. This provides more flexibility than DBMS_SESSION.

Trace is enabled at session level using

    EXECUTE dbms_support.start_trace;

With no parameters, this procedure enables level 1 trace

Event 10046 level 4 trace can be enabled using

    EXECUTE dbms_support.start_trace (binds=>true);

Event 10046 level 8 trace can be enabled using

    EXECUTE dbms_support.start_trace (waits=>true);

Event 10046 level 12 trace can be enabled using

    EXECUTE dbms_support.start_trace (binds=>true,waits=>true);

Trace can be disabled using

    EXECUTE dbms_support.stop_trace;

Enabling Trace in another Session

Trace can be enabled in another session using the DBMS_SUPPORT package

The SID and optionally the serial number if the target session must be obtained from V$SESSION. The serial number can optionally be specified as 0.

For example to enable level 1 trace in a session with SID 9 and serial number 29 use

    EXECUTE dbms_support.start_trace_in_session (9,29);

With no parameters, this procedure enables level 1 trace

Event 10046 level 4 trace can be enabled using

    EXECUTE dbms_support.start_trace_in_session (9,29,binds=>true);

Event 10046 level 8 trace can be enabled using

    EXECUTE dbms_support.start_trace_in_session (9,29,waits=>true);

Event 10046 level 12 trace can be enabled using

    EXECUTE dbms_support.start_trace_in_session (9,29,binds=>true,waits=>true);

Trace can be disabled using

    dbms_support.stop_trace_in_session (9,29);

Trace can be also be enabled in another session using the DBMS_SYSTEM package

The SID and the serial number of the target session must be obtained from V$SESSION. In this case the serial number must be specified

For example to enable trace in a session with SID 9 and serial number 29 use

    EXECUTE dbms_system.set_sql_trace_in_session (9,29,TRUE);

Note this is equivalent to enabling event 10046 level 1

To disable trace in the same session use

    EXECUTE dbms_system.set_sql_trace_in_session (9,29,FALSE);

Event 10046 trace can also be enabled in another session using the DBMS_SYSTEM package

The SID and the serial number of the target session must be obtained from V$SESSION.

For example to enable event 10046 level 8 in a session with SID 9 and serial number 29 use

    EXECUTE dbms_system.set_ev (9,29,10046,8,'');

To disable event 10046 in the same session use

    EXECUTE dbms_system.set_ev (9,29,10046,0,'');

Enabling Trace in Oracle5

This is probably only of historic interest today, but SQL trace was included in Oracle5. Thanks to Martin Berg and Mogens Norgaard for the following information

Trace can be enabled in Oracle5 using the command

    SELECT TRACE ('trace_name',1),1 FROM dual;

To disable trace in Oracle5 use the command

    SELECT TRACE ('trace_name',1),0 FROM dual;

where trace_name is one of the following

Trace Name Description
SQL Parser
ACCESS Optimizer
KNL Kernel Access
RWST Row Source Static Details
RWSD Row Source Dynamic Details
SORTSTAT Sort Statistics
SORTRUNS Disk Sort Details
CTXSTG Context Area

For example to enable SQL trace in Oracle5 use the command

    SELECT TRACE ('sql',1),1 FROM dual;

To disable SQL trace in Oracle5 use the command

    SELECT TRACE ('sql',1),0 FROM dual;

Enabling Trace in Oracle6

In Oracle6, trace can be enabled at instance level using the sql_trace init.ora parameter

Trace can be enabled at session level using

    ALTER SESSION SET SQL_TRACE TRUE

Trace can be disabled at session level using

    ALTER SESSION SET SQL_TRACE FALSE

Note that the syntax is different in Oracle6 from Oracle7

Note also that this information has been taken from the Oracle6 manuals and has not yet been verified on an Oracle6 database