解密CBO下SQL计划生成步骤

最新推荐文章于 2024-07-11 11:14:34 发布

wenzhongyan

最新推荐文章于 2024-07-11 11:14:34 发布

阅读量1.5k

点赞数

分类专栏： ORACLE-技术研究文章标签： sql 解密 join oracle statistics access

本文链接：https://blog.csdn.net/wenzhongyan/article/details/7950186

版权

ORACLE-技术研究专栏收录该内容

37 篇文章 2 订阅

订阅专栏

前言

CBO优化器模式下，我们在查看一条SQL的执行计划时，只看到CBO最终告诉我们的执行计划结果，但是并不知道CBO为什么要选择这个执行计划，特别是当这个执行计划明显失真时，我们非常想搞清楚到底为什么CBO会判断出这个执行计划的成本为最低，是什么原因导致CBO做出了这样一个错误的选择。幸运的是，Oracle为我们提供了一个10053事件，通过它我们能够窥探CBO究竟在选择执行计划过程中做了那些分析和判断，从而解开CBO执行计划选择的神秘面纱。

关键字：CBO、执行计划、选择

10053事件介绍

10053事件在Oracle官方文档上是找不到任何关于它的信息，但是Oracle的很多专家或支持人员已经将这个事件公诸于众，它可以把Oracle是如何分析SQL执行计划的成本并最终确定‘最优‘的过程一步一步记录在trace文件中，我们可以通过查看这个trace文件从而了解Oracle是如何分析和选择SQL执行计划的，可以说10053事件真正为我们撕掉了蒙在CBO身上的神秘面纱。

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事件trace文件。

启用10053事件

10053事件的使用方法和其他跟踪事件的使用方法一样，首先要启动该事件，这个时候要设置一个该事件的运行级别，然后是运行你想分析跟踪的SQL语句，这时就会在指定目录（$ORACLE_BASE/admin/sid/udump）中生成trace文件，当然最后还要终止事件防止一直生成trace导致空间不足，接下来我们就实际演示下：

10053事件trace文件同样存放在$ORACLE_BASE/admin/sid/udump目录下，我们查看这个trace文件的时候要注意，不能用tkprof工具格式化，因为tkprof工具只能处理sql_trace或者10046事件产生的trace文件，而10053的trace文件只能通过直接阅读这个原始文件。

TRACE内容讲解

通过上面的操作，我们就得到了一个分析SQL执行计划的trace文件，现在我们就来解读这个生成的trace文件，我们通过记事本把这个文件打开，首先得到如下的信息：

这是10053 trace文件的第一部分，同所有类型的trace文件一样，列出了一些环境的基本信息，包括操作系统、数据库版本和会话信息，一目了然，这里就不在介绍。

从Predicate Move-Around (PM)部分开始，才是正式进入10053的trace信息部分，这部分CBO主要的工作就是对刚才分析的SQL语句的谓语进行重新分析、重写，把它重组为最符合逻辑的SQL语句。不如我们这里的SQL语句最初的谓语形式为：

"A"."病人ID"=183 AND "A"."主页ID"=1 AND "A"."病人ID"="B"."病人ID"

现在通过重组，改写为：

"A"."病人ID"=183 AND "A"."主页ID"=1 AND "A"."病人ID"="B"."病人ID" AND "B"."病人ID"=183

可以看出，从逻辑上，这两个谓语条件是等价的，CBO把它改写为这样，主要是为了更方便计算每一步的成本和估算基质。比如我们这条SQL语句既要访问”住院病人记录”的病人id列，也需要访问”病人信息”的病人id列，定义值都是183，CBO可以按照这个条件去估算每个操作的结果集。

接下来：

这一部分是名词解释，它对一些trace文件可能会用到的一些缩写指标的含义进行标注，这些指标在trace文件中可能会经常被用到，所以在trace文件的开头会把它们列出来，这也是trace文件人性化的一个表现，方面我们更加容易地阅读trace文件。

接下来的部分是一些修复BUG的信息，以及会影响SQL执行计划判断分析相关的初始化参数值，我们也可以从这里看到其实影响CBO执行计划判断的参数还是蛮多的，而我们最常调整的optimizer_index_cost_adj和optimizer_index_caching就在其中，且由于这个参数我们作了调整，并不是数据库默认值，trace文件会把它们列在最前面，让我们一目了然。

接下来这一部分是SQL语句中引用到的对象的基本信息，包括所有的表和各个索引的信息，通过这里可以看出统计信息在CBO分析执行计划中重要性，因为CBO就是通过这些值计算每个执行计划的代价。

我们可以看到包括表名、表的行数、数据块数、平均行长和谓语条件中包含字段的直方图，对于在谓语中没有出现的字段，因为它不影响执行计划的选择，所以CBO不需要考虑它的代价，我们在这里可以看到，主要列出了病人id这个字段，另外对于表上所有索引的统计信息，包括索引高度、索引叶块数（LB）、每个索引键值所占的数据块数（LB/K）、每个索引键值对应表中的数据块数（DB/K）、索引的聚合因子（CLUF），这些都是CBO判断执行计划代价的基础，了解了这些指标对于我们分析SQL执行计划很有用处，当SQL语句执行计划出现异常的时候，我们可以从这些方面进行分析，思考。

接下来这个就是重点了，trace展示CBO计算的每个对象单独访问的代价，CBO要计算出每个对象单独访问时的代价，通过比较所有的数据访问的代价，从而选择出代价最小的一种访问方式，我们来看看每种访问的代价，由于篇幅所限，我们这里只讲解下“住院费用记录”CBO的分析情况。

这里我们首先讲下Card:Original和Card:Rounded，前者表示原记录数，也就是这个表中所有的记录数，在这里就表示“住院费用记录”有2709347条记录，而后者表示输出的记录数，这个是CBO估算出通过条件过滤，预计能得到的记录数，并不是真实的，但是也比较接近，通过这个结果，CBO会给出几种访问数据的方式，如本案例CBO这里就给出了两种访问方式：

全表扫描：

Access Path:TableScan

索引全顺序扫描:

Access Path:index（AllEqRange）

然后会分别给出两种访问的代价：

全表扫描：14188.13

索引全顺序扫描:42.46

最终CBO在评估这两种访问代价后，给出了最佳访问方式，当然是通过索引比较合理：

Best:: AccessPath: IndexRange Index: 住院费用记录_IX_病人ID

同样的道理，CBO也会分析“病人信息”表的各种访问方式的代价，并选出最佳的访问方式，这里就不在列出，道理是一样的，但是这一部分只是CBO计算每个表单独进行数据访问的代价，最终表与表之间还要进行关键查询，下面的部分就是CBO列出“病人信息”，“住院费用记录”所有的关联方式，并计算出每种关联方式的代价，最终选出代价最小的关联方式作为SQL的执行计划，下面我们来看下这部分的trace信息：

这里我们同样只重点分析一部分内容，我们看上面，首先CBO后列出所有表连接顺序的方式，如上是第一种可能的表连接顺序：Join order（1）：病人信息#0，住院费用记录#1，同样的，如果表连接比较多，还有可能有其他Join order（2）、Join order（3）…连接顺序，然后在这种连接顺序的基础上，CBO列出了三种连接方式，嵌套连接（NL Join），排序合并连接（SM Join）和哈希连接（HA Join），并分别就这三种表连接方式，列出其连接代价，如下：

表连接顺序：“病人信息“ 关联 “住院费用记录”

嵌套连接（NL Join）代价

全表扫面方式： 14189.13

索引顺序扫描：住院费用记录_IX_病人id 41.26

排序合并连接（SM Join） 44.54

哈希连接（HA Join） 42.46

可以看出，最终CBO判断出在这种表连接顺序下，采用嵌套连接（NL Join），并且通过索引（住院费用记录_IX_病人id）的访问代价是最低的（41.26），其他连接顺序也是同样的比较方式，最终CBO会通过比较代价，计算出所有连接顺序和连接方式的最优组合，最后这一部分就是CBO通过上面的计算给出的一个执行计划报告，前面操作的所有cost值都在这个执行计划中有所表现。