oracle性能优化之10053跟踪

jlthlx

　　一. 10053事件
　　当一个SQL出现性能问题的时候，可以使用SQL_TRACE 或者 10046事件来跟踪SQL. 通过生成的trace来了解SQL的执行过程。 我们在查看一条SQL的执行计划的时候，只能看到CBO 最终告诉我们的执行计划结果，但是不知道CBO 是根据什么来做的。 如果遇到了执行计划失真，如：一个SQL语句，很明显oracle应该使用索引，但是执行计划却没有使用索引。无法进行分析判断。
　　而10053事件就提供了这样的功能。它产生的trace文件提供了Oracle如何选择执行计划，为什么会得到这样的执行计划信息。
　　10053事件生成trace文件目录和SQL_TRACE一样。
　　在Oracle 10g中，SQL_TRACE生成的trace文件默认路劲是$ORACLE_BASE/admin/SID/udump.
　　在Oracle 11g，trace 默认路径在：$ORACLE_BASE/diag/rdbms/orcl/orcl/trace目录下
　　对于10053事件的trace文件，我们只能直接阅读原始的trace文件，不能使用tkprof工具来处理，tkprof工具只能用来处理sql_trace 和 10046事件产生的trace文件。
　　10053事件有两个级别：
　　Level 2：2级是1级的一个子集，它包含以下内容：
　　Column statistics
　　Single Access Paths
　　Join Costs
　　Table Joins Considered
　　Join Methods Considered (NL/MS/HA)
　　Level 1： 1级比2级更详细，它包含2级的所有内容，在加如下内容：
　　Parameters used by the optimizer
　　Index statistics
　　启用10053跟踪：
SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';　　
Session altered.
　　
SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 2';
　　
Session altered.
　　关闭10053事件跟踪：
SQL> ALTER SESSION SET EVENTS '10053 trace name context off';　　
Session altered.
　　二． 实验10053事件：
　　1.设定当前的trace 文件
　　1.1 设定trace 文件名称
SQL> alter session set tracefile_identifier='10053';　　
Session altered.
　　设置标识的目的就是方便我们查找生成的trace文件。我们只需要在trace目录查找文件名里带有标识的文件即可。
　　通过以下sql也可以查到trace文件的位置：
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
　　
17             WHERE   NAME = 'user_dump_dest') d;
　　trace_file_name
　　--------------------------------------------------------------------------------
　　/u01/app/oracle/diag/rdbms/phub/PHUB/trace/phub_ora_8894.trc
　　2.启动10053事件
SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';　　
Session altered.
　　3.执行事务
SQL> select * from emp a,dept b where a.deptno=b.deptno and a.deptno=20;SQL> explain plan for select * from emp a,dept b where a.deptno=b.deptno and a.deptno=20;　　
Explained.
　　四、10053事件内容解析
　　1.  Predicate Move-Around (PM)(对SQL语句的谓词进行分析、重写，把它改为最符合逻辑的SQL语句)
　　2.  解释trace文件用到的一些缩写的指标定义
　　3.  Peeked values of the binds in SQL statement(绑定变量的描述)
　　4.  Bug Fix Control Environment(一些修复的bug信息)
　　5.  PARAMETERS WITH DEFAULT VALUES(性能相关的初始化参数)
　　6.  BASE STATISTICAL INFORMATION(SQL引用对象的基本信息)
　　7.  CBO计算每个对象单独访问的代价
　　8.  CBO计算列出两个表关联方式，并计算出每一种关联方式的代价，最终选择最小的cost