怎样理解trace信息的收集

本篇文章为大家展示了怎样理解trace信息的收集，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

每逢与遇到SQL相关性能，我们总是需要收集10046的，来查看和诊断问题。

因为10046真实的反应的SQL语句执行的时候的真实信息，解析，执行，获取的时间消耗，row source operation的具体情况。

具体等待事件，每个时间具体的时间消耗等等。希望下面的Case有一种就能帮助到您。

EVENT: 10046 "enable SQL statement tracing (including binds/waits)" (Doc ID 21154.1)

Interpreting Raw SQL_TRACE output (Doc ID 39817.1)

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

==================

SQL性能常用：

所有版本

    10046 on session/system

To start tracing:

    Alter session/system(慎用) set events 10046 trace name context forever, level 12;

/* execute your selects to be traced */

    To stop tracing

    Alter session/system(慎用) set events 10046 trace name context off;

11g以上

    1. event++在system级别指定sql_id,对新起的会话和当前的会话有效， 对其他已经存在的会话无效

SQL> alter system set events sql_trace [sql: 5qcyrymp65fak] level=12;

         注释：当前事件对当前的session和新创建的session有效，对已经存在的其他session无效。

         关闭 event ++：

SQL>  alter system set events sql_trace [sql: 5qcyrymp65fak] off;

    2. event ++ 指定某个process的sql_id

SQL> oradebug setospid   <<<<<指定检测的会话的spid   <<<<<<<<<<

         SQL> oradebug unlimit

         SQL> oradebug tracefile_name

SQL> oradebug event sql_trace [sql: 5qcyrymp65fak] level=12

         关闭 event ++：

SQL>  oradebug event sql_trace [sql: 5qcyrymp65fak] off

    3. 不知道SQL_ID手动执行SQL收集10046

    SQL>connect username/password

SQL>alter session set timed_statistics = true;

    SQL>alter session set statistics_level=all;

SQL>alter session set max_dump_file_size = unlimited;

    SQL> select value from v$diag_info where name=Default Trace File;   <<<<在11g以上工作

SQL> variable a1;   <<<<<请执行类型

    SQL> exec :a1 := 123123或abded;   <<<<<<<请设置数值或字符串

SQL>alter session set events 10046 trace name context forever, level 12;

SQL>UPDATE /*+ RESTRICT_ALL_REF_CONS */ "LBI_ODS"."T_O_CUSTOMER_ACCOUNT" SET

    "ACCOUNT_TYPE_ID" = :a1

    WHERE

"ACCOUNT_NO" = 1234565;                                     <<<<<<<<<<<<执行sql重现问题

    SQL>alter session set events 10046 trace name context off; 

==================

使用Trigger设置10046

    Use a Logon TriggerTo 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;

    /

==================

MMON的10046

1. 请打开auto purge的trace？

    begin

      dbms_monitor.serv_mod_act_trace_enable

               (service_name=>SYS$BACKGROUND,

module_name=>MMON_SLAVE,

               action_name=>Auto-Purge Slave Action);

    end;

    /

2. 请至少等待一天，请您明天查看时候auto purge被执行，并产生m00x trace文件包含10046

    3. 关闭auto purge的trace

    begin

      dbms_monitor.serv_mod_act_trace_disable

(service_name=>SYS$BACKGROUND,

               module_name=>MMON_SLAVE,

               action_name=>Auto-Purge Slave Action);

    end;

    /

==================

Data pump 10046

    1. enable 10046 trace for DM/DW process

alter system set events sql_trace{process: pname=dw | pname=dm} level=12;

2. Please reproduce the issue, then add "TRACE=480300" in data pump importing command

    3. Please upload data pump importing log and the generated DM/DW process trace

To disable the tracing by issuing:

    alter system set events sql_trace {process : pname = dw | pname = dm} off; 

==================

其他方式设置10046

    1. DBMS_SUPPORTTo 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 SessionThe examples below demonstrate how to trace session with SID=18 and Serial# =226 obtained from V$SESSION.

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

3. 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, );

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

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

http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_monitor.htm#CFAHBEAB

   CLIENT_ID_STAT_DISABLE Procedure

   CLIENT_ID_STAT_ENABLE Procedure

CLIENT_ID_TRACE_DISABLE Procedure

   CLIENT_ID_TRACE_ENABLE Procedure

   DATABASE_TRACE_DISABLE Procedure

   DATABASE_TRACE_ENABLE Procedure

SERV_MOD_ACT_STAT_DISABLE Procedure

   SERV_MOD_ACT_STAT_ENABLE Procedure

SERV_MOD_ACT_TRACE_DISABLE Procedure

   SERV_MOD_ACT_TRACE_ENABLE Procedure

   SESSION_TRACE_DISABLE Procedure

   SESSION_TRACE_ENABLE Procedure

6. 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 ;

上述内容就是怎样理解trace信息的收集，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注行业资讯频道。