10046 trace详解(1)

10046 trace详解(1)

    10046trace帮助我们解析一条/多条SQL、PL/SQL语句的运行状态，这些状态包括：Parse/Fetch/Execute三个阶段中遇到的等待事件、消耗的物理和逻辑读、CPU时间、执行计划等，它为我们揭示了一条、多条SQL的运行情况，对SQL调优是一个非常好的辅助工具，同时还能帮我们分析一些DDL维护命令的内部工作原理，RMAN、DataPump Expdp/impdp等工具缓慢问题。

   记得进公司面试时提到这个一样问题，查看SQL执行计划有哪此方法，10046、PL/SQL F5快捷键、EXPLAIN PLAN FOR....、TOAD也可以查看，其实10053也可以查看SQL的执行计划，那么10046和10053的区别是什么呢？10053是最常用的ORACLE优化器optimizer跟踪trace，10053可以作为我们解析优化器为什么选择某个执行计划，其中理由的辅助工具，但并不告诉我们这个执行计划到底运行的如何。而10046并不解释optimizer优化器的工作，但它同样说明了在SQL解析Parse阶段所遇到的等待事件和所消耗的CPU资源，以及Execute执行和Fetch阶段的各项指标，简单来说，10046告诉我们SQL插计划运行情况，10053告诉我们优化器为什么选择这个执行计划。

    通常为了诊断SQL调优类问题，我们需要记录下这些语句在执行过程中产生的等待以及bind variables(绑定变量)的信息。这些信息可以通过级别为12的10046 trace获得。下面的例子列举了在各种场景下，如何设定10046事件。

一、Trace文件的位置
    从11gR1开始，Oracle引入了新的诊断结构，以参数DIAGNOSTIC_DEST控制存放trace文件与core文件的路径。可以用以下命令，获取DIAGNOSTIC_DEST的位置：

   SQL> show parameterdiagnostic_dest;

   11gR1以前,果是用户进程，10046trace文件会被生成在user_dump_dest下,如果是后台进程，trace文件会被生成在background_dump_dest下,下面的命令可以显示user_dump_dest:

   SQL> show parameteruser_dump_dest

   注：通过设定tracefile_identifier，通过这个设置可以帮助我们更容易的找到生成的trace文件。

二、在Session级打开trace
   这种方法适用于SQL语句可以在新的session创建后再运行。

   alter session settracefile_identifier='10046'; 
    altersession set timed_statistics = true;
    altersession set statistics_level=all;--实例级别设置此选项需要注意，会消耗大量的CPU
   alter session set max_dump_file_size = unlimited;
    altersession set events '10046 trace name context forever,level12'; --常用的级别是12
   执行需要被trace的SQL

    select* from dual;
   exit;
   如果不退出当前session, 可以用以下命令关闭trace:

   alter session set events '10046 trace namecontext off';

   注意，如果session没有被彻底地关闭并且跟踪被停止了，某些重要的trace信息的可能会丢失。
   注意：这里我们将"statistics_level"设置为all，这是因为有可能这个参数在系统级不是默认值"TYPICAL"（比如BASIC）。为了收集性能相关问题的信息我们需要打开某个级别的statistics。我们推荐在 session 级将这个参数设置成ALL 以便于收集更多的信息，尽管这不是必须的。

三、跟踪一个已经开始的进程
   如果需要跟踪一个已经存在session，可以用 oradebug连接到session上，并发起10046trace。
    首先，用某种方法找到需要被跟踪的session.
   例如，在SQL*Plus里，找出目标session的OS的进程ID(spid):
   selectp.PID,p.SPID,s.SID
    fromv$process p,v$session s
    wheres.paddr = p.addr
    and s.sid =&SESSION_ID
   /

   SPID 是操作系统的进程标识符（ospid）
   PID是Oracle的进程标识符(ora pid)

    假设需要被跟踪的OSPID是9834，以sysdba的身份登录到SQL*Plus并执行下面的命令：

      oradebug setospid 9834
   oradebug unlimit
   oradebug event 10046 tracename context forever,level12
    注：也可以通过oradebug使用 'setorapid'命令连接到一个session。

    下面的例中，使用 PID（Oracle进程标识符）(而不是SPID), oradebug命令将被改为：

  connect / as sysdba
   oradebug setorapid9834
   oradebug unlimit
   oradebug event 10046 tracename context forever,level 12

    跟踪过程完成以后，关闭oradebug跟踪：

    oradebug event 10046 trace name context off

四、实例层的跟踪
   注意:在实例层设置跟踪需要非常小心，这是因为整体性能会由于所有session都被跟踪而受到影响，这个设置将会跟踪在这个参数设置“以后”创建的每个session。已经存在的session不会被跟踪，系统层的10046跟踪适用于当我们知道问题session会出现，但是不能预先识别它的时候，在这种情况下，可以打开系统层跟踪一小段时间，当问题被重现以后立即将其关闭，然后从已经生成的trace中查找需要的信息，用以下命令打开系统层的跟踪：

   alter system set events '10046 trace name context forever,level12'

用以下命令关闭在所有session中的10046跟踪：

   alter system set events '10046 trace name context off';
五、通过初始化参数设置进行实例层的跟踪
   设置以下参数并重新启动实例后，实例上所有的session都会打开跟踪。

   event="10046 trace name context forever,level12"

   如果要关闭可以通过移除这个参数并且重启实例，或者使用下面的altersystem命令可以关闭跟踪。

   alter system set events '10046 trace name context off';

六、通过logontrigger设置跟踪
  有的时候当需要跟踪某个特定用户的操作时，可以使用logon trigger来打开跟踪

  CREATE OR REPLACE TRIGGER SYS.set_trace
   AFTER LOGON ON DATABASE
   WHEN (USER like'&USERNAME')
   DECLARE
   lcommand varchar(200);
   BEGIN
   EXECUTE IMMEDIATE 'altersession set tracefile_identifier=''From_Trigger''';
   EXECUTE IMMEDIATE 'altersession set statistics_level=ALL';
   EXECUTE IMMEDIATE 'altersession set max_dump_file_size=UNLIMITED';
   EXECUTE IMMEDIATE 'altersession set events ''10046 trace name context forever, level12''';
   ENDset_trace;
   / 

   需要注意的事，为了能打开跟踪session,执行trigger的用户需要被显式地授予'alter session'权限

   grantalter session to user_xj

七、用SQLT收集trace
   SQLTXPLAIN的Xecute方法生成的诊断文件中会包含10046trace，正如XECUTE这个名字所示，SQLT会执行被分析的SQL语句，然后生成一个诊断文件集（包括10046trace）。详见：
   Note:215187.1 SQLT (SQLTXPLAIN) - Toolthat helps to diagnose SQL statements performing poorly (Doc ID215187.1)

   用这种方法生成的trace文件会被包含在SQLT输出包中， 格式为：

   sqlt_s12345_10046_execute.trc  --其中12345是SQLT报告ID。
八、用DBMS_MONITOR进行跟踪
   DBMS_MONITOR包提供几个打开跟踪的方法。详细请见：
   Note:293661.1 Tracing Enhancements In 10g UsingDBMS_MONITOR
   如果您运行的是PL/SQL存储过程或包，那么可以通过使用PL/SQLprofiler判断PL/SQL运行期间的时间消耗的具体信息。下面的文档介绍了PL/SQLprofiler的使用方法：
   Note:243755.1 Implementing and Using the PL/SQL Profiler

九、其它特定场景下打开跟踪的方法
    Note:21154.1EVENT: 10046 "enable SQL statement tracing(including binds/waits)"
   Note:1274511.1 General SQL_TRACE / 10046 trace Gathering Examples
   Note:160124.1 How to Set SQL Trace on with 10046 Event Trace whichProvides the Bind Variables
   Note:371678.1 Capture 10046 Traces Upon User Login (without using atrigger)
   Note:1102801.1 How to Get 10046 Trace for Parallel Query
   Note:242374.1 Tracing PX session with a 10046 event or sql_trace
   Note:258418.1 Getting 10046 Trace for Export and Import

十、Trace文件解析

    Note:199081.1 SQL_TRACE (10046), TKProf and Explain Plan - OverviewReference
   Note:39817.1 Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACEoutput
   Note:224270.1 Trace Analyzer TRCANLZR - Interpreting Raw SQL Traceswith Binds and/or Waits 

                 generated by EVENT 10046
   NOTE:41634.1 - TKProf Basic Overview
   NOTE:32951.1 - TKProf Interpretation (9i and below)
   NOTE:760786.1 - TKProf Interpretation (9i and above)
   NOTE:214106.1 - Using TKProf to compare actual and predicted rowcounts
   Note:199083.1 * Master Note: SQL Query Performance Overview
   Note:398838.1 * FAQ: SQL Query Performance - Frequently AskedQuestions

前一篇： DBMS_SQLTUNE详解(2)---sql_profile

后一篇： 10046 trace详解(2)--tkprof

参考：
百度
interpreting+raw+SQL_TRACe++and+DBMS_
interpreting raw SQL_TRACe and DBMS_