常规SQL_TRACE / 10046 trace收集示例 (Doc ID 1274511.1)

General SQL_TRACE / 10046 trace Gathering Examples (Doc ID 1274511.1)

GOAL

This article illustrates numerous methods by which session tracing may be initiated.
本文介绍了可以启动Session跟踪的多种方法。

SOLUTION

Note: To Collect Trace for Diagnosis Performance issues, it is recommended to use :
注意：要收集用于诊断性能问题的Trace，建议使用：
Note:376442.1 * How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues

也可以参考文章：https://blog.csdn.net/u010692693/article/details/103081054

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.
下面的示例演示如何跟踪从V$SESSION获得的SID=18 and Serial# =226的会话。

使用 "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);

使用 "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, '');

使用 "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);

使用 "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);

使用 Oradebug（作为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 */

NOTE: The following syntax can also be used within the if logic to also get the user name or to add more granularity by specifying a host name where the connection originated.
注意：在if逻辑中，还可以使用以下语法来获取用户名或通过指定发起连接的主机名来增加粒度。
IF SYS_CONTEXT('USERENV','SESSION_USER') = '<USER_NM>' AND SYS_CONTEXT('USERENV','HOST') = '<HOST_NM>' THEN

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;
/