Oracle获取session的trace

1. 使用参数SQL_TRACE

 下面是官网对此参数的说明

SQL_TRACE

Property	Description
Parameter type	Boolean
Default value	false
Modifiable	ALTER SESSION, ALTER SYSTEM
Range of values	true | false

SQL_TRACE enables or disables the SQL trace facility. Setting this parameter to true provides information on tuning that you can use to improve performance. You can change the value using the DBMS_SYSTEM package.

Caution:

Using this initialization parameter to enable the SQL trace facility for the entire instance can have a severe performance impact. Enable the facility for specific sessions using the ALTER SESSION statement. If you must enable the facility on an entire production environment, then you can minimize performance impact by:

• Maintaining at least 25% idle CPU capacity

• Maintaining adequate disk space for the USER_DUMP_DEST location

• Striping disk space over sufficient disks

See Also:

Oracle Database Performance Tuning Guide for more information about performance diagnostic tools

Note:

The SQL_TRACE parameter is deprecated. Oracle recommends that you use the DBMS_MONITOR and DBMS_SESSION packages instead. SQL_TRACE is retained for backward compatibility only.

 

    可以在initxxx.ora 文件中设置此参数.

    alter system set SQL_TRACE = TRACE  scope=both;

    alter session set SQL_TRACE = TRUE; ----给当前session开启trace.

11G 之前获取session对应的trace文件的完整路径 可以使用下面的SQL

SELECT    a.VALUE || b.symbol || c.instance_name || '_ora_' || d.spid || '.trc' trace_file
  FROM (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') a,
       (SELECT SUBSTR (VALUE, -6, 1) symbol FROM v$parameter
         WHERE NAME = 'user_dump_dest') b,
       (SELECT instance_name FROM v$instance) c,
       (SELECT spid FROM v$session s, v$process p, v$mystat m
         WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d
/

11G 还可以使用新增的v$diag_info 视图直接找

 SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';

生产的trc 文件 可以用oracle自带的tkprof进行处理，原始的trc也能看出很多信息，但是没那么直观，处理后会好很多.

 

2. 使用10046事件

10046 事件按照收集信息内容，可以分成4个级别：
Level 1： 等同于SQL_TRACE 的功能
Level 4： 在Level 1的基础上增加收集绑定变量的信息
Level 8： 在Level 1 的基础上增加等待事件的信息
Level 12：等同于Level 4+Level 8, 即同时收集绑定变量信息和等待事件信息。
 
对当前session 使用10046事件
SQL>alter session set events ‘10046 trace name context forever, level 12’; --启动10046事件
执行相关事务
SQL>alter session set events ‘10046 trace name context off’; -- 关闭10046事件

 

3. 使用PL/SQL包

SQL_Trace和10046事件都不能单独给别人的session开启trace，下面的PL/SQL包能做到。

1. DBMS_MONITOR 
2. DBMS_SESSION
3. DBMS_SYSTEM(默认只有sys才能执行,其他用户需要执行必须先授权.)
4. DBMS_SUPPORT(The DBMS_SUPPORT package is not present by default, but can be loaded as the SYS user by executing the "@$ORACLE_HOME/rdbms/admin/dbmssupp.sql" script.)

 

• DBMS_MONITOR

With the advent of Oracle 10g the SQL tracing options have been centralized and extended using the DBMS_MONITOR package. The examples below show just a few possible variations for enabling and disabling SQL trace in Oracle 10g.

-- Oracle 10g
SQL> EXEC DBMS_MONITOR.session_trace_enable;
SQL> EXEC DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.session_trace_disable;

SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id=>1234, serial_num=>1234);
SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id =>1234, serial_num=>1234, waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.session_trace_disable(session_id=>1234, serial_num=>1234);

SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>'tim_hall');
SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>'tim_hall', waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.client_id_trace_disable(client_id=>'tim_hall');

SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_enable(service_name=>'db10g', module_name=>'test_api', action_name=>'running');
SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_enable(service_name=>'db10g', module_name=>'test_api', action_name=>'running', -
> waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.serv_mod_act_trace_disable(service_name=>'db10g', module_name=>'test_api', action_name=>'running');

The package provides the conventional session level tracing along with two new variations. First, tracing can be enabled on multiple sessions based on the value of the client_identifier column of the V$SESSIONview, set using the DBMS_SESSION package. Second, trace can be activated for multiple sessions based on various combinations of the service_name, module, action columns in the V$SESSION view, set using the DBMS_APPLICATION_INFO package, along with the instance_name in RAC environments. With all the possible permutations and default values this provides a high degree of flexibility.

 

给当前session设置 client_identifier

exec dbms_session.set_identifier('myid');

 

给当前session设置module , action

exec dbms_application_info.set_module('test','youraction');

 

• DBMS_SESSION

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;

 

DBMS_SYSTEM

 

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

DBMS_SUPPORT

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