怎样理解trace信息的收集

共计 4914 个字符，预计需要花费 13 分钟才能阅读完成。

本篇文章为大家展示了怎样理解 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   指定检测的会话的 spid  select spid from V$process, V$session where audsid=userenv(SESSIONID) and paddr=addr;
  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 the type of ACCOUNT_TYPE_ID   请执行类型
  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 信息的收集，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注丸趣 TV 行业资讯频道。