首先介绍与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中使用下面的语法命令:
SQL> connect / as sysdba SQL> oradebug setmypid SQL> oradebug unlimit SQL> oradebug event 10053 trace name context forever, level 1 SQL> ...enter your query here... SQL> oradebug event 10053 trace name context off SQL> oradebug tracefile_name /chia/web/admin/PTAV3/udump/ptav3_ora_15365.trc |
"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要么使用一个内部固定的值,要么使