In this Document\
| Goal |
|---|
| Solution |
|---|
| Pre-requisites for successful tracing |
|---|
| Tracing the Current Session |
|---|
| SQL_TRACE |
|---|
| 10046 |
|---|
| DBMS_SUPPORT |
|---|
| Tracing from Another Session |
|---|
| Using "dbms_system.SET_BOOL_PARAM_IN_SESSION" |
|---|
| Using "dbms_system.set_ev" |
|---|
| Using "dbms_system.set_sql_trace_in_session" |
|---|
| Using "sys.dbms_monitor" |
|---|
| Using Oradebug (as SYS) |
|---|
| Use a Logon Trigger |
|---|
| References |
|---|
APPLIES TO:
Oracle Database - Enterprise Edition - Version 9.2.0.8 to 11.2.0.3 [Release 9.2 to 11.2]
Information in this document applies to any platform.\
GOAL
This article illustrates numerous methods by which session tracing may be initiated.
\
SOLUTION
Note: To Collect Trace for Diagnosis Performance issues, it is recommended to use :
Note:376442.1 * How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues
Pre-requisites for successful tracing
Set <PARAMETER: TIMED_STATISTICS> to TRUE
Set <PARAMETER: MAX_DUMP_FILE_SIZE> to a high value or œunlimited
Tracing the Current Session
SQL_TRACE
Start tracing:
ALTER SESSION SET SQL_TRACE = TRUE ;
/* execute your selects to be traced */
Stop tracing:
ALTER SESSION SET SQL_TRACE = FALSE;
10046
To start tracing:
Alter session set events '˜10046 trace name context forever, level 12';
/* execute your selects to be traced */
To stop tracing
Alter session set events '10046 trace name context off';
DBMS_SUPPORT
To start tracing:
exec sys.dbms_support.start_trace ;
/* execute your selects to be traced */
To stop tracing:
exec sys.dbms_support.stop_trace ;
Tracing from Another Session
The examples below demonstrate how to trace session with SID=18 and Serial# =226 obtained from V$SESSION.
\
Using "dbms_system.SET_BOOL_PARAM_IN_SESSION"
To start tracing:
exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION(18, 226, 'sql_trace', TRUE);
/* execute your selects to be traced */
To stop tracing:
exec sys.dbms_system.SET_BOOL_PARAM_IN_SESSION(18, 226, 'sql_trace', FALSE);
Using "dbms_system.set_ev"
To start tracing:
exec dbms_system.set_ev(18, 226, 10046, 12, '');
To stop tracing:
exec dbms_system.set_ev(18, 226, 10046, 0, '');
Using "dbms_system.set_sql_trace_in_session"
To start tracing:
exec dbms_system.set_sql_trace_in_session(18,226,TRUE);
/* execute your selects to be traced */
To stop tracing:
exec dbms_system.set_sql_trace_in_session(18,226,FALSE);
Using "sys.dbms_monitor"
To start tracing:
exec sys.dbms_monitor.session_trace_enable(session_id=>18,serial_num=>226, waits=>true, binds=>true);
/* execute your selects to be traced */
To stop tracing:
exec sys.dbms_monitor.session_trace_disable(session_id=>18,serial_num=>226);
Using Oradebug (as SYS)
To start tracing:
oradebug setospid xxxx
oradebug event 10046 trace name context forever, level 12;
/* In the session being traced execute the selects */
To stop tracing:
oradebug event 10046 trace name context off ;
Use a Logon Trigger
To start tracing:
create or replace trigger user_logon_trg
after logon on database
begin
if USER = 'xxxx' then
execute immediate
'Alter session set events ''10046 trace name context forever, level 8''';
end if;
end;
/
/* Login a new session as User 'xxxx' and execute your selects to be traced */
To stop tracing: via LogOff Trigger (needs to be created before logging off)
create or replace trigger user_logoff_trg
before logoff on database
begin
if USER = 'xxxx' then
execute immediate
'Alter session set events ''10046 trace name context off''';
end if;
end;
/
