• Welcome
• Projects
• Presentations
• Research

Enabling Trace

There are several ways to enable trace:

Enabling Trace using ORADEBUG

SQL_TRACE is a synonym for event 10046 and can be set using ORADEBUG

The syntax is:

SQL> ORADEBUG DOC EVENT NAME SQL_TRACE

sql_trace: event for sql trace

Usage
-------
sql_trace
   wait            < false | true >,
   bind            < false | true >,
   plan_stat       < never | first_execution | all_executions | adaptive >,
   level           

The default level is 1.

For example to enable default trace:

ORADEBUG EVENT SQL_TRACE

To enable trace including bind variables:

ORADEBUG EVENT SQL_TRACE BIND=TRUE

To enable trace including bind variables and wait events:

ORADEBUG EVENT SQL_TRACE BIND=TRUE,WAIT=TRUE

To enable trace including plan statistics for every statement execution:

ORADEBUG EVENT SQL_TRACE PLAN_STAT=ALL_EXECUTIONS

Numeric event levels are still supported:

ORADEBUG EVENT SQL_TRACE LEVEL=4

To disable trace:

ORADEBUG EVENT SQL_TRACE OFF

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;

In Oracle 11.1 and above, SQL_TRACE is an event and can be enabled using the following syntax:

ALTER SYSTEM SET EVENTS 'sql_trace wait=true';
ALTER SYSTEM SET EVENTS 'sql_trace bind=true';
ALTER SYSTEM SET EVENTS 'sql_trace bind=true,wait=true';
ALTER SYSTEM SET EVENTS 'sql_trace plan_stat=all_executions';

In Oracle 11.1 and above trace can be restricteed to a specific SQL ID by specifying a filter:

ALTER SYSTEM SET EVENTS sql_trace 
[sql: sql_id=3s1yukp05bzg6] bind=true, wait=true';

Multiple SQL IDs can be specified using the | symbol as a separator:

ALTER SYSTEM SET EVENTS sql_trace 
[sql: sql_id=3s1yukp05bzg6|aca4xvmz0rzup] bind=true, wait=true';

In Oracle 11.1 and above, trace can be restricted to specific components. For example:

ALTER SYSTEM SET EVENTS 'trace[rdbms.SQL_Optimizer.*]';

The library name can optionally be ommitted. For example:

ALTER SYSTEM SET EVENTS 'trace[SQL_Optimizer.*]';

Disable trace again using:

ALTER SYSTEM SET EVENTS 'trace[SQL_Optimizer.*] off';

Individual components can also be filtered. For example:

ALTER SYSTEM SET EVENTS 'trace[SQL_Optimizer.*][sql:3bnxc7htmf2ad]';

In all versions 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;

In Oracle 11.1 and above, SQL_TRACE is an event and can be enabled using the following syntax:

ALTER SESSION SET EVENTS 'sql_trace wait=true';
ALTER SESSION SET EVENTS 'sql_trace bind=true';
ALTER SESSION SET EVENTS 'sql_trace bind=true,wait=true';
ALTER SESSION SET EVENTS 'sql_trace plan_stat=all_executions';

In Oracle 11.1 and above trace can be restricteed to a specific SQL ID by specifying a filter:

ALTER SYSTEM SET EVENTS sql_trace 
[sql: sql_id=3s1yukp05bzg6] bind=true, wait=true';

Multiple SQL IDs can be specified using the | symbol as a separator:

ALTER SYSTEM SET EVENTS sql_trace 
[sql: sql_id=3s1yukp05bzg6|aca4xvmz0rzup] bind=true, wait=true';

In Oracle 11.1 and above, trace can be restricted to specific components. For example:

ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*]';

The library name can optionally be ommitted. For example:

ALTER SESSION SET EVENTS 'trace[SQL_Optimizer.*]';

Disable trace again using:

ALTER SESSION SET EVENTS 'trace[SQL_Optimizer.*] off';

Individual components can also be filtered. For example:

ALTER SESSION SET EVENTS 'trace[SQL_Optimizer.*][sql:3bnxc7htmf2ad]';

In all versions 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:

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