oracle调优之Trace方法及相关工具总结02

oracle调优之Trace方法及相关工具总结01

3.2.      10053（CBO TRACE）

3.2.1. 使用方法

10053事件是用来诊断优化器如何估算成本和选择执行计划的，用它产生的trace文件提供了Oracle如何选择执行计划，为什么会得到这样的执行计划信息。
10053事件生成trace文件目录和SQL_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
1、启用10053事件
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
2、关闭10053事件
ALTER SESSION SET EVENTS '10053 trace name context off';

3.2.2. 条件

 要实现跟踪必须满足两个条件：

sql语句必须被hard parse并且必须使用CBO优化器模式。如果sql语句已经被parse过，那么10053不生成跟踪信息。

如果你使用RULE优化器，那么10053 也不会生成跟踪信息。

可使用sql查询当前会话的优化器模式：

SELECT NAME, VALUE FROM v$parameter WHERE NAME = 'optimizer_mode';

CBO优化器：基于代价， 优化模式：ALL_ROWS,FIRST_ROWS

RBO优化器：基于规则， 优化模式：RULE

对查询语句开启10053跟踪，满足硬解析和CBO优化器模式条件:

SELECT * FROM hr_operating_units hr WHERE hr.organization_id = 82;

3.2.3. 设置其他SESSION的10053

可以设置其他session的10053
开启：SYS.DBMS_SYSTEM.SET_EV (<sid>, <serial#>, 10053, {1|2}, '')
关闭：SYS.DBMS_SYSTEM.SET_EV (<sid>, <serial#>, 10053,0, '')

3.2.4. 文件介绍

1、第一部分，文件顶端的信息，是TRACE文件的通用信息，包含了操作系统、数据库、会话信息。

2、第二步，解释TRACE文件用到的一些缩写指标的含义。

3、第三部分，与性能相关的的初始化参数的值。

4、第四部分，10053的TRACE解释部分

5、第五部分，主要是对sql语句的谓词进行分析、重写，把它改为最符合逻辑的sql语句

6、第六部分，绑定变量的描述

7、第七部分，这一部分列出了3个对象，它们是T表，T1表，T表的索引IND_T。还可以告诉我们表是否有做统计分析（没有做统计分析为NOT ANALYZED）。

8、第八部分，计算CBO访问每个单独对面的代价。

在oracle中有表访问方式的说法，访问表中的数据主要通过三种方式进行访问：

   1、全表扫描（full table scan），直接访问数据页，查找满足条件的数据。

   2、通过rowid扫描（table access by rowid），如果知道数据的rowid，那么直接通过rowid进行查找。

   3、索引扫描（index scan），如果一个表创建了索引，那么可以通过索引来找出我们想要的数据在表中的存放位置，也就是rowid，通过返回rowid然后用rowid来进行访问具体数据。

   4、而索引扫描中又可分为索引全扫描（index full scan）、索引范围扫描（index range scan）和索引唯一扫描（index unique scan）等。

这里有两个指标对于我们分析计划比较重要：

Card: Original: 2914.000000 ：源记录数，也就是操作数据的输入记录数，在这里就是表的实际记录数为2914.

Rounded: 1：输出的记录树，CBO计算出通过条件过滤，预计得到的记录数。我们知道表T满足条件的记录数为1。这里估算出得也是1。

通过这一部分信息我们可以看到对表，CBO认为可以通过下面的方式访问数据：

Access Path: TableScan  Cost:  1.25。这里因为表没有索引，且都是子查询，所以只列出了一种方式认为最优。一般还会有其他方式，例如：

这里就可以根据所花费的时间，选择最优的访问方式。

9、第九部分，列出表之间所有关联方式所花费的代价。最终选出代价最少的的执行计划，总共会有六种情况：

NESTED LOOPS: 嵌套循环联接

SORT MERGE: 合并联接

HASH: 物理运算符

   A.  order[1] X$KSPPI关联X$KSPPCV：

  B. order[2]X$KSPPCV关联X$KSPPI：

C.  最终选择关联方式

10、第十部分，通过第九部分的计算CBO给出的一个执行计划报告，并选出最终的执行计划。