【IT168 技术文档】

　　首先介绍与CBO计算成本有关的一些参数说明，介绍了CBO在查询中如何计算成本。接着给出10053跟踪文件分析的一般方法，最后结合一个具体跟踪文件，对如何分析10053跟踪文件进行详细阐述。

　　1. 关于Oracle案例学习

　　Oracle案例学习主要是作为一种工具，主要提供对复杂事件、进程、过程以及一系列相关事件的信息与知识。每个案例都是在处理实际问题的经验基础上编写的。

　　每个学习案例包含一定的技能级别。技能级别指文档的阅读者在学习此案例之前，应该具备什么样的技能级别。

　　有以下几个级别：

　　 专家级：在相关主题领域有丰富经验

　　 中级：在相关主题领域有一定的经验

　　 入门级：在相关主题领域没什么经验

　　本案例建议的级别：专家级

　　2. 案例学习摘要

　　本案例使用跟踪文件，阐述分析10053跟踪文件的方法体系。注意，10053事件跟踪文件主要用于辅助oracle开发者以及技术支持来诊断优化器相关的问题，它随着新版本或者补丁集而有所变化。本文的主要目的不是对10053跟踪文件提供全面的参考，而是介绍oracle工程师怎样使用这种跟踪文件。

　　同时，我们将窥探CBO在查询中如何计算成本，以及CBO是如何最终获得执行计划的。

　　需要指出的是，CBO在估算成本的时候，会随着版本的变化，其算法会有不同。

　　这里我们将会分析一个糟糕的执行计划，并判断出CBO是如何计算成本并导致不好的计划的。我们会在不同地方比较10053跟踪，但主要精力还是放在这个糟糕的计划是如何计算成本的。良好的执行计划一般而言比不好的执行计划(其中没有考虑使用索引)更简短。

　　检查10053的原因一般是为了搞清楚CBO为什么这样进行选择。10053可以帮我们回答诸如“索引为什么没被使用”之类的问题。“CBO为什么要选择全表扫描?” 。一般来说，10053不是处理性能问题时最先使用的方法---在这方面，查看执行计划并使用tkprof工具能更好的获取信息。10053用于深入CBO进行选择的原因分析。

　　3. 案例历史

　　执行一个未使用提示的sql语句(一个包含3种连接的select语句)需要9个小时，而如果使用"NO_INDEX"提示，将会在4分钟内执行完。表使用的是分区表，而且用到了并行查询。另外，用户将"OPTIMIZER_INDEX_CACHING"设置为70(不知用户为什么这么做，我们猜测可能是因为当时他们并没有得到想要的执行计划才这么做)。此参数设置效果可以使单块索引I/O下降70%。

　　使用10053跟踪，获取未加提示(“不好的”)的执行计划与加提示(“好的”)的执行计划。两个执行计划的主要不同点是，未加提示的执行计划使用nested嵌套循环连接方式，内层连接使用的是INDEX FULL SCAN(不是index fast full scan)操作来作为内层行集。加提示的执行计划使用哈希连接，INDEX FAST FULL SCAN (IFF)操作的结果集作为内层行集。

　　注意，就本案例而言，新版本中使用10053产生的跟踪文件可能会发生很大变化。

　　一般而言，新版本的跟踪文件比较容易阅读，在"预分析工作"部分需要做的工作较少。

　　4. 预分析工作

　　在开始分析跟踪文件前，我们首先进行一些观测，了解一些CBO计算成本时的有关因素。有时，这些参数和因素的值会很好的告诉我们，为什么从诸多执行计划中选择了特定的执行计划。

　　要收集10053事件的跟踪文件，可以在sqlplus中使用下面的语法命令：

　　"oradebug tracefile_name"会显示10053产生的跟踪路径与文件名。

　　 A) 确认查询被跟踪了

　　这步很重要，因为我们想确认所跟踪的是相关查询的跟踪信息。在跟踪文件的QUERY部分找到sql语句，并确认该sql就是我们所关心的sql语句。在10g版本中，如果没有使用绑定变量，QUERY部分在跟踪文件的结尾，否则，QUERY部分就在跟踪文件的开始。注意，搞清楚我们关心的QUERY部分跟哪些跟踪信息关联。有时候，很容易误以为跟踪文件尾部的QUERY部分就是想要跟踪的信息(这在10g中没有使用绑定变量的sql语句中很容易发生)。

　　 B) 参数

　　OPTIMIZER_FEATURES_ENABLE = 9.2.0

　　_OPTIMIZER_PERCENT_PARALLEL = 101

　　OPTIMIZER_INDEX_CACHING = 70

　　此参数会影响索引访问的成本，使用索引的成本为原始成本乘以(100 - optimizer_index_caching)/100。所以，本案例中，会用以下的因子相乘，来减少索引使用的成本：(100 - 70)/100 = 0.3或者大约1/3。这就是说，索引成本乘以0.3，即为不使用此参数情况下成本的1/3。注意，索引I/O成本根据"BLEVEL", "LEAF_BLOCKS", 以及 "CLUF" (群集因子)的值来计算。这个参数只影响与BLEVEL和LEAF_BLOCKS有关的成本部分。CLUF影响对表访问的成本，参数OPTIMIZER_INDEX_CACHING对其不会有影响。

　　OPTIMIZER_INDEX_COST_ADJ = 99

　　此参数用下面的分数来表示索引访问成本的百分比：optmizer_index_cost_adj / 100。本案例中，该分数为99/100 或者 0.99。该参数会影响所有的索引成本，即使在连接中使用的索引也一样受影响。

　　OPTIMIZER_DYNAMIC_SAMPLING = 1

　　该参数控制CBO多大程度的依赖于动态样本以便获取集的势和选择率的信息，集的势和选择率会在计算访问路径的成本时用到。如果设置为1，表示仅仅当查询中表的统计信息缺失时才会使用样本统计方法。

　　_OPTIMIZER_COST_MODEL = CHOOSE

　　如果设置为CHOOSE，而且已收集系统的统计信息，CBO将使用新的CPU模型。如果设置为I/O，将会使用旧成本模型，忽略CPU成本。

　　DB_FILE_MULTIBLOCK_READ_COUNT = 64

　　该参数控制执行全表扫描或者索引扫描时的成本。该参数的值越高，执行全表扫描或者索引扫描时成本越低。该参数的值被CBO要么按照固定公式(如果OPTIMIZER_COST_MODEL = io)计算，要么从收集的实际统计信息中计算并进行参考。

　　_CPU_TO_IO = 0 (默认)

　　该参数用于量度在使用CPU和I/O成本来计算总成本时，一次I/O成本需要的CPU周期。如果设置为0，即默认值，CBO要么使用一个内部固定的值，要么使用系统统计信息派生的一个值(统计信息包含CPU转速，单块I/O时间，多块I/O时间，多块I/O时所读的平均块数目)。当CBO考虑CPU成本时，为了确认成本，判断CBO使用该参数的什么值非常重要。

　　 C) 计算CBO使用的CPU对I/O比率

　　为了确定CBO用到的_CPU_TO_IO的值，在10053中，必须找到一个入口，此入口包含下述参数的值：CPU 成本, I/O 成本, 总成本。通过获取这三个值，使用相关的公式，我们可以计算CBO内部使用的这个比率。

　　_CPU_TO_IO的值在10053中是个常量，在任何计算中都是一样的，因此任何入口中显示的这三个变量的值，对于计算_CPU_TO_IO的值都是有用的。需要注意的是，选择一个CPU和I/O成本较大的值，因为它们的值越大，算出来的结果就越准确。

　　找一个index fast full (IFF)扫描，或者一些包含CPU，IO与总体成本有关的信息。　

　　使用公式

　　综合的Resc Cost = (RSC_CPU cost / _cpu_to_io) + RSC_IO Cost

　　计算 _cpu_to_io:

　　_cpu_to_io = RSC_CPU Cost / (Combined Cost - RSC_IO Cost)

　　= 2865229980 / (55630 - 52693)

　　= 975563.49 CPU cycles per IO

　　 D)计算多块读除数

　　当CBO估算全表扫描或者索引快速全扫描的成本时，它会使用除数去除表或者索引的总块数，这个除数估算了每次从磁盘上进行的物理读时将要读入的块数(这里称为多块读除数MBDivisor)。以前，参数"DB_FILE_MULTIBLOCK_READ_COUNT”是估算多块读除数的一个基数(为了补偿实际环境下的限制，它的值通过公式进行减少的计算)，在9.2及其以后的版本中，多块读除数的值的估算在统计信息收集了的情况下，有很大的变化。

　　在分析10053之前，计算出CBO使用的这个除数，能帮助我们快速了解它的值是不是合理的。较低的值会导致CBO使用FTS和IFF时候的成本较索引访问路径要昂贵，较高的值，会使FTS和IFF的成本较低廉。

　　为了获得这个除数，找一个简单的表访问路径入口，获取表扫描("tsc")的资源成本("Resc")。然后查找表包含的总块数。计算公式如下：

　　tsc cost = Num Blocks / MBDivisor

　　MBdivisor = Num Blocks / tsc cost

　　例如：

　　在"Base Statistical Information"部分:

　　在"Single Table Access Path"部分:

　　Mdivisor = Nblks / tsc

　　Mdivisor = 125272 / 116982 = 1.07

　　注意：这个值有点低。查看系统统计信息以及了解这个值是如何计算的，这非常有用。较低的值会使FTS与IFF相对索引扫描来说较昂贵。也许这些值是真实的，但也可能系统统计信息是在过去某个时间获取过，但是不能反应系统负荷的实际状况。考虑FTS可能较昂贵，客户可能觉得他们进一步要使用index_cost_adjustment参数来降低索引成本。很容易看出，这个数据库在很多情况下，将倾向于使用一种索引访问的方式而不是FTS(全表扫描)。

　　多块读除数已经很低，这很可能阻碍CBO选择非索引的访问路径，这种情况下，客户将OPTIMIZER_INDEX_COST_ADJ设置得如此的高，看起来有点不寻常。了解一下客户以前为什么要设置这个值会很有帮助。

　　 E)查看" BASE STATISTICAL INFORMATION"与" SINGLE TABLE ACCESS PATH"部分，检查缺失或者不充分的统计信息：

　　典型问题包括：

　　表或者索引没有统计信息

　　如果一个对象没有收集统计信息，你会看到一些消息，比如："(NOT ANALYZED)"。

　　但是对于索引，不会有消息明确的指出它们没有被分析过。因此，你只能读索引的统计信息，并查看默认统计信息。LEAF_BLOCKS默认统计信息是25，CLUSTERING_FACTOR是800。

　　对于分区对象，需知道是否有全局或者分区级别统计信息被收集了。

　　为了检测global only(非分区级别统计信息被收集)，查找UNANALYZED分区。例如(下述数据不是来自当前实例的跟踪信息)：　

　　不幸的是，没办法弄清楚系统的总体统计信息的收集，是采用全局样本还是单个的分区表的方式。如果有疑问，全局样本最好是查看DBA_TABLES.GLOBAL_STATS，如果为YES，则表示全局统计信息已被收集。

　　 无直方图

　　为特定的列查找"No Histogram". 例子 (下述数据不是当前实例的追踪文件):　

　　5. 分析 小结

　　分析10053的过程，一般是从跟踪文件的底部往上看，再到感兴趣的区域。感兴趣的区域就是那些属于看起来有问题的执行计划的区域。

　　在这里列举进行10053跟踪文件分析的步骤：

　　1) 从文件末尾开始

　　确认你感兴趣的sql语句存在于跟踪文件中。如果跟踪文件中有EXPLAIN PLAN，编辑一下跟踪文件尽量便于阅读。

　　2)查看所选执行计划的最终成本。

　　3)查看产生最终成本的连接顺序

　　4)查看感兴趣的引起成本的连接顺序部分

　　5)查看连接顺序产生成本的连接类型

　　6)检查在步骤5中找到的连接类型的成本

　　a)确认用到何种访问路径

　　b)检查其他被拒绝的访问路径(仅适用于嵌套循环连接，在对内部行集的访问路径中，有几个访问路径产生成本)

　　 详细分析

　　1.从结尾开始，从执行计划开始

　　在10053中获得准确的执行计划输出，很重要。有些信息在10053中会丢失，我们需要使用执行计划来得出成本计算是如何进行的结论。有时候我们会使用执行计划来调查10053或者检查我们的分析。

　　注意，根据parent ID来编排执行计划的步骤，使得执行计划的层次具有可读性。

　　请注意，包含在跟踪文件中的执行计划的输出会随着版本的变化而不同，而且经常不会出现。在10g中，可以通过调整10053跟踪信息，然后执行一次EXPLAIN PLAN FOR....命令，来获取一个格式化好了的执行计划。然而，绑定变量的出现可能会影响实际产生的执行计划，EXPLAIN PLAN命令发现不了这一点，因为不同的绑定变量值有不同的访问路径。

　　2.查看执行计划的最终成本

　　好的执行计划 (在另外一个跟踪文件中)，用到了NO_INDEX提示，看起来像下面这样:

　　3.查找产生最终成本的连接顺序

　　使用cost (20762)作为关键字，查找产生此成本的连接顺序，我们找到如下：

　　它属于序号为2的连接顺序.... 上翻到连接顺序部分的开始处，可以看到:

　　Join order[2]: PREMIUM_PLAN_CODE [A13] CERT_INSURED_PLAN_BENEFIT_FACT [A11] CERTIFICATE [A12]

　　在9206中查找选定的执行计划的连接顺序，在10g中，会更简单，我们在10053中有如下的跟踪信息：

　　此例中，被选择的"JOIN ORDER"是 2.

　　4. 查找"好的"执行计划与"不好的"执行计划中不一样的连接顺序部分

　　本例中，它们第二张表的连接顺序不同:

　　"好的"执行计划

　　"不好的"执行计划　

　　5.在连接顺序部分查找连接，它们是第四步中找到的产生成本的连接.

　　跟踪文件中往上查找成本20266，你会找到如下行：　

　　继续查找是哪个连接产生了这个(最低)成本　

　　从执行计划中，我们知道，它将是个NL连接，并且在这里得到了确认。在NL连接部分继续查找此成本。

　　这里，我们看到"resp"成本的非常好的成本被找到。这就是"response time"成本，例如，一个执行计划使用PX在尽可能短的时间内找到答案所消耗的成本。"resc" 成本就是 "resource cost"。当串行执行查询的时候，这就是资源消耗成本。完整的执行计划输出中会显示PX是否被使用。

　　我们需要弄清楚这个成本是怎么计算出来的。为此，我们要看看CBO是如何计算连接成本的。下面是基本计算公式，用实际值进行替换：

　　注意：在下面的公式中，"RESC(outer)"指访问内层表的资源消耗。"RESP(outer)"指外层表的响应成本(使用PX)　

　　这种连接排列为外层表和并行内层表的并行子表使用并行操作，但每个子表对内层表使用完全索引访问路径。既然是个NL连接，很可能使用了"broadcast"PX行分布。如果没有执行计划，很难知道这是否是CBO的选择。

　　因此，不是成本除以4，而是除以3.6

　　外层表的全表扫描的并行度可以在这里看到。连接的并行度设置为其中任何一张表的最高并行度。本例中，设置为表PREMIUM_PLAN_CODE的并行度。一个完整的执行计划输出对于验证这一点非常有帮助。

　　6. 表"CERT_INSURED_PLAN_BENEFIT_FACT"的其他NL连接成本

　　 使用FTS：

　　Resc = Resc(outer) + [Card(outer) * Resc(inner) ]

　　= 63646 + [1 * 167233 ] (Note: Resc(Inner) is close to the value in single table access path, but not exact)

　　= 230879 (exact)

　　Resp = Resp(outer) + [Card(outer) * Resp(inner) ]

　　= 15809 + [ 1 * 41309 ]

　　= 57118 vs. 57618 (close, not exact)

　　 使用索引快速全扫描：

　　 其他连接类型的成本(SMJ 与 HJ)　　

          Resc cost = Resc(outer) + Resc(inner) + Sort_Cost(outer) + Sort_Cost(inner)

　　= Resc(outer) + Resc(inner) + [ (CPU_Cost(outer) + IO_Cost(outer) ) + (CPU_Cost(inner) + IO_Cost(inner) ]

　　= 63646 + 55630 + ( 975652 / 975563.49 + 0 ) + ( 27269777197 / 975563.49 + 233891)

　　= 381120.8 ~ 381119 (very close)

　　Resp cost = Resp(outer) + Resp(inner) + Par_Sort_Cost(outer) + Par_Sort_Cost(inner)

　　= Resp(outer) + Resp(inner) + [ (Par_CPU_Cost(outer) + Par_IO_Cost(outer) ) + ( (Par_CPU_Cost(inner) + Par_CPU_Cost(inner)) ]

　　= 15809 + 27815 + [ ( 1084058 / 975563.49 + 0 ) + ( 14577786635 / 975563.49 + 129941) ]

　　= 188509.1 ~ 188508 (very close)

　　Resc cost = Resc(outer) + Resc(inner) + HJ_Cost_Ser

　　= 63646 + 55630 + (HJ_Resc_Cost * HJ_Dop)

　　= 63646 + 55630 + ( 587 * 4 )

　　= 121624 ~ 121625 (very close)

　　Resp cost = Resp(outer) + Resp(inner) + HJ_Cost_Par

　　= 15809 + 27815 + 587

　　= 44211 ~ 44212 (very close)

　　注意：

　　用于SMJ 和 HA 的RESC 与RESP成本来自于每张表的单个表访问成本部分，例如：

　　RESC(inner)是IFF resc成本，RESP(inner) 是IFF resp成本。

　　7. 结论

　　本文的最终结论是，未加提示("不好的")执行计划，CBO选择一个NL连接的全索引扫描。主要受以下因素影响：

　　1. FTS的高成本(由于"multiblock read divisor"的值过低)。

　　2. 由于OPTIMIZER_INDEX_CACHING参数，导致索引访问的成本较低。这个参数极大的降低索引访问的成本，对系统来说远远超出合理的值。

　　我们知道另外一个较好的执行计划使用全表扫描，并且具有较好的性能，可以看到，"multiblock read divisor"在本例子中设置好像不是很准确。一个较准确的除数，会让FTS/IFF的成本更低，使用哈希连接会更好。另外一个更好的执行计划可能是对内部行源(与哈希连接类似，但是避免了哈希成本)使用IFF嵌套循环。本例中，嵌套循环的内部行源没有选择使用IFF，是因为它的成本计算没有考虑OPTIMIZER_INDEX_CACHING因子(只应用于执行单块索引读操作)所带来的成本减少的益处。而且，IFF的相对成本由于较低的multiblock read divisor设置而增加了。

　　后续的调查包括：

　　 找出为什么多块读除数如此低(在aux_stats$视图中检查系统的统计)

　　 一段时间间隔后，再次收集系统的统计信息，将这些信息放置于"stattab"中，比较与aux_stats$中的现存值。

　　 了解客户为什么要设置OPTIMIZER_INDEX_CACHING为一个如此高的值。

　　8. 参考文档

　　Note: 338137.1