This procedure, which was introduced in Oracle 10.1, enables event 10046 trace in the specified session
Argument Name | Type | In/Out | Default? |
SESSION_ID | BINARY_INTEGER | IN | DEFAULT |
SERIAL_NUM | BINARY_INTEGER | IN | DEFAULT |
WAITS | BOOLEAN | IN | DEFAULT |
BINDS | BOOLEAN | IN | DEFAULT |
PLAN_STAT | VARCHAR2 | IN | DEFAULT |
If the SESSION_ID is NULL and SERIAL_NUM is NULL then trace is enabled in the current session
If the SESSION_ID is NOT NULL and SERIAL_NUM is NULL specified then trace is enabled in a session with the specified SESSION_ID
If the SESSION_ID is NOT NULL and SERIAL_NUM is NOT NULL then trace is enabled in the specified SESSION_ID/SID
If SESSION_ID is NULL and SERIAL_NUM is NOT NULL an error is returned
There are two optional Boolean parameters, WAITS and BINDS, both of which default to FALSE.
In Oracle 11.1 and above PLAN_STAT specifies the dump frequency for row source (STAT) statistics. Values can be:
PLAN_STAT is useful when individual statement executions are affected by data cardinality / selectivity
In Oracle 10.2 (at least) the default parameters enable event 10046 level 8 (waits) trace
For example to enable event 10046, level 8 trace in the current session use:
EXECUTE dbms_monitor.session_trace_enable
This is equivalent to:
ALTER SESSION SET EVENTS '10046 trace name context forever, level 1';
To enable event 10046, level 4 trace (bind variables) in the current session
EXECUTE dbms_monitor.session_trace_enable (binds=>true);
This is equivalent to:
ALTER SESSION SET EVENTS '10046 trace name context forever, level 4';
To enable event 10046, level 8 trace (waits) in the current session:
EXECUTE dbms_monitor.session_trace_enable (waits=>true);
This is equivalent to:
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
To enable event 10046, level 1 trace in a session with SID 9, serial number 29 use:
EXECUTE dbms_monitor.session_trace_enable (9, 29);
This is equivalent to:
EXECUTE dbms_system.set_ev (9, 29, 10046, 1, '');
To enable event 10046, level 4 trace (bind variables) in a session with SID 9, serial number 29 use:
EXECUTE dbms_monitor.session_trace_enable (9, 29, binds=>true);
This is equivalent to:
EXECUTE dbms_system.set_ev (9, 29, 10046, 4, '');
To enable event 10046, level 8 trace (waits) in a session with SID 9, serial number 29 use:
EXECUTE dbms_monitor.session_trace_enable (9, 29, waits=>true);
This is equivalent to:
EXECUTE dbms_system.set_ev (9, 29, 10046, 8, '');