SQL Tuning 基础概述03 - 使用sql_trace和10046事件跟踪执行计划

alter session set sql_trace = true; //开始sql_trace

alter session set tracefile_identifier = jytrace; //设定trace文件的标识符

alter session set sql_trace = false; //结束sql_trace

exec dbms_system.set_sql_trace_in_session(sid,serial#,true); //开始

exec dbms_system.set_sql_trace_in_session(sid,serial#,false); //结束

alter system set sql_trace = true; //开始

alter system set sql_trace = false; //结束

10046事件是Oracle提供的内部事件，是对SQL_TRACE的增强.
10046事件可以设置以下四个级别:
1 - 启用标准的SQL_TRACE功能,等价于sql_trace
4 - Level 1 + 绑定值(bind values)
8 - Level 1 + 等待事件跟踪
12 - Level 1 + Level 4 + Level 8
类似sql_trace，10046事件可以在全局设置，也可以在session级设置。

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

alter session set events ‘10046 trace name context off‘;

exec dbms_monitor.session_trace_enable(sid,serial#,waits=>true,binds=>true); //开始

exec dbms_monitor.session_trace_disable(sid,serial#);  //结束

SELECT      d.VALUE
         || ‘/‘
         || LOWER (RTRIM (i.INSTANCE, CHR (0)))
         || ‘_ora_‘
         || p.spid
         || ‘.trc‘
            AS "trace_file_name"
  FROM   (SELECT   p.spid
            FROM   v$mystat m, v$session s, v$process p
           WHERE   m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
         (SELECT   t.INSTANCE
            FROM   v$thread t, v$parameter v
           WHERE   v.NAME = ‘thread‘
                   AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
         (SELECT   VALUE
            FROM   v$parameter
           WHERE   NAME = ‘user_dump_dest‘) d;

tkprof xxx.trc xxx.txt explain=system/oracle  sys=n