编辑手记：2016 Oracle 技术嘉年华活动已经结束，为了让更多的技术爱好者有机会获取大会干货资源，我们将不断邀请演讲嘉宾展开“2016 Oracle 线上嘉年华”。

讲师简介

邓秋爽（小鱼）

云和恩墨专家，有超过5年超大型数据库专业服务经验，擅长oracle 数据库优化、SQL优化和troubleshooting

今晚的恩墨大讲堂将有我为大家分享SQL审核中的两个典型案例，如下：

1复合索引前导列选择

2绑定变量分享——某业务SQL性能问题分析

在介绍这两个case之前先对目前我们的SQL审核交付服务做一个简单说明，我们目前的SQL审核交付服务包括以下几个部分：

• 待上线SQL评审，预估性能瓶颈——控制系统新上线SQL质量

• 线上系统SQL性能监控、优化——持续优化系统线上SQL，降低系统资源消耗

• 线上系统预警、故障诊断分析——及时对系统故障进行干预和处理，最快响应客户的请求

• 数据库版本变更、优化器参数调整评估——重大数据库参数调整评估，数据库版本变更评估

• 对业务需求、设计进行评审——对表、索引结构设计进行咨询——业务切合数据库设计，最有效的管控系统风险

SQL审核服务工作两年来，我们一直都是在做线上和待上线SQL代码的审核分析，通过专业的SQL审核团队和方法我们取得了一定的成效，系统上线变更均没有出现重大故障，得到了客户的认可。

言归正常，接下来我们来分享这两个case：

1、复合索引前导列选择

待上线SQL审核发现核心业务模块的某SQL是大表全表扫描

执行计划如下：

这里我们看到，有一个全表扫描，优化器评估的是 263k，

以下是表的统计信息：

我们看到有3.2亿的数据量。

可能说到优化全表扫描，大家都会想到要创建索引，那么这个索引如何创建呢？

在执行计划的第五部分谓词部分我截取如下：

这里有service ID 和enddate两个谓词，我们要通过对这两个谓词创建索引进行优化，我们看统计信息里面，enddate的不同值有1628520个，serviceID的不同值有402个，而表有3.2亿的数据量，是很大的。

 

那么如何创建索引？可能有以下三种方案：

1、  serviceID+enddate复合索引

2、  enddate+serviceID复合索引

3、  enddate的单列索引

 

我们在创建索引的时候有以下原则：

核心业务SQL要求SQL执行计划尽量最高效，而刚好这个SQL是个核心业务SQL，用户需要响应时间最可能最短，由于复合索引的效率一般是远优于单列索引的，所以enddate的单列这里不再考虑

 

再看serviceid、enddate两列如何创建复合索引

我们首先来看serviceID+enddate的复合索引

这个索引在索引范围扫描的过程中消耗的逻辑读是33，回表次数达到373。

 接下来是enddate+serviceID的复合索引：

这个在索引范围扫描的过程消耗的逻辑读是873，回表次数是1213次。

 

为什么两者的索引范围扫描消耗的逻辑读相差这么大。我们来看看谓词部分。

 前者serviceid+enddate复合索引索引范围扫描全部走access，access就是说我在索引范围扫描的时候，在扫描页块节点中是不存在多余的页块的，也就是扫描的每一个叶块都是满足SQL的谓词条件的。

 

enddate+serviceid复合索引索引范围扫描enddate字段走access、serviceid走filter，因为在非等值列作为索引前导时，索引范围扫描叶块找到满足enddate的条件后，在双向链表下做索引扫描并不能保证扫描的每一个叶块都是满足serviceid条件的，所以需要取出来之后再过滤一次，这样就多了一次filter的操作。

 

因此我们看到，两者的区别就在索引范围扫描这里。在第二种方式中，读取的页块里面存在无效的页块。

 

复合索引的前导的选择： 

1.索引的前导尽量让更多的核心业务sql能够使用，这样能够避免再另外创建单独的单列索引

2.对于单独的核心业务的sql语句，要求该SQL的性能尽可能最优，对于同时出现等值和非等值条件（比如范围、like等）时复合索引应该尽量选择where条件中等值条件列作为索引的前导列。例如where条件中同时出现state=X and create_time>sysdate-1，此时应该创建state+create_time的复合索引，state字段作为索引的前导列。

 

接下来跟大家分享一个跟绑定变量有关的内容

关于绑定变量的问题在OLTP系统中我们经常会遇到，因为在OLTP中存在很多SQL代码都是搞并发的，所以我们希望在SQL语句中尽量能使用到绑定变量，绑定变量最重要的作用就是代码重用 

• 绑定变量主要作用：

用于代码重用，降低系统的硬解析成本

• 绑定变量的弊端：

绑定变量窥视副作用、自适应游标缺陷、影响执行计划的评估

• 绑定变量如何使用：

结合绑定变量窥视、直方图、字段的业务类型综合考虑

 

接下来我们来看一个案例

执行计划是全表扫描，cost计算为1433，Oracle版本12.1.0.2? 带入具体值单次逻辑读和物理读需要4万多，平均返回0行数据，属于核心业务SQL（同业类类型的SQL还有一批）

 

在这条SQL中，不存在任何的分组或者聚合函数，返回0行数据走全表扫描多半不是最合理的执行计划

我们来看它的执行计划

下面是这张表的统计信息

我们看到processcode的不同值只有112个，number bucket有111，而表的数据量达到139万，我们看到这一列上是有索引的，那这里优化器是否应该走索引更高效了？

1、processcode是倾斜字段且已收集直方图，查询表数据得知processcode=‘UMCPMTELSTE_ZT’占表中数据量比例极少。

2、_optim_peek_user_binds被置为FALSE,窥视特性关闭。

3、索引没有失效、无sql profile、hint等其他影响。

由于processcode=‘UMCPMTELSTE_ZT’占有表中数据比例极少，索引范围扫描此时应该是最合理的执行计划，优化器难道cost计算错了？

 

添加hint/*+index(a IDX_INTERFACE_COMM_PROCESSCODE)*/比较cost成本

我们看到成本是2513，是远远大于之前的全表扫描的成本的。

 

CBO根据成本计算会选择全表扫描的执行计划，那么如何优化了：

我想很多DBA都遇到过执行计划异常的情况，我们采取最轻量级的解决方式，就是通过sql profile、sql plan baseline等绑定执行计划。但是这三种都有一个弊端，如果SQL text的变化，比如列的增加或者减少，这时候sql profile、sql plan baseline就会失效。

 

第二种方式就是通过hint 固化代码。但是这种方式也存在一个问题，我们刚刚提到processcode并不是一个“均配”字段，比如说A对应的值是极少数的，而B对应的数据很多。

那这种情况下我们如果把代码固定的话，如果别的SQL传入的是B，则对于这个业务的SQL执行计划并不是最合理的。

 

还是回到SQL优化的问题，我们不能避开它，看执行计划的时候，一定要注意谓词的部分，我们发现无论是索引范围扫描还是全表扫描，Oracle的优化器在评估的时候一定是先去评估processcode=：BIZID，评估该谓词条件后通过查询转换来进一步校验‘UMCPMTELSTE_ZT’=:BIZID是否合理

这里我们得出在where条件中同时出现processcode=：BIZID和processcode=具体值（UMCPMTELSTE_ZT）的时候，优化器这里是从最左边开始考虑各个执行计划的执行成本，然后通过查询转换在filter阶段再去校验‘UMCPMTELSTE_ZT’=:BIZID是否合理。

如果说优化器能优先考虑到processcode=‘UMCPMTELSTE_ZT’具体值，则CPU是否会自动选择索引范围扫描呢？

 

我们将具体值和绑定变量做个替换，where processcode=‘UMCPMTELSTE_ZT’and processcode=：BIZID，执行计划如下：

Oracle在索引范围扫描的时候会直接考虑processcode=‘UMCPMTELSTE_ZT’，然后再第二步的时候检查具体值是否等于绑定变量，做一个逻辑校验。

大家仔细看可能会发现，上面的那段代码事实上是不严谨的，processcode=：BIZID和processcode=具体值（UMCPMTELSTE_ZT）同时出现，而实际的绑定变量都传入的等于前面那个具体值，如果是为了校验传入变量是否和具体值一致也没必要丢给数据库去判断，而开发人员这种写法则导致了该SQL没有选择合适的索引，这种SQL 在评审阶段都是需要我们关注的。

 

这里我们也提一下个人认为使用绑定变量的标准供大家参考：

 绑定变量 - 哪些列适合使用绑定变量

绑定变量窥视关闭、收集直方图环境下：

1、建议使用绑定变量的列：对于流水号、订单号、用户ID、电话号码、身份证

号码、证件号码等，这类列的特点是列的唯一性较好、列的不同值数量跟表的

数据量比较接近，而这类列涉及的sql语句往往存在高并发，写成绑定变量会

大大减少数据库的解析成本。

2、不建议使用绑定变量的列：能够枚举的列（比如状态类型字段，这部分类型

字段往往不同值较少，一般即使上千万的数据表中该列也只有十多个、几十个

的不同值), 即使不写绑定变量也只会产生极少数的sql_id，这部分的oracle

解析消耗微乎其微，并且这种类型的列有可能存在倾斜数据，而如果使用绑定

变量，则对于倾斜数据的评估并不准确，可能导致该列无法走索引范围扫描、

或者影响表JOIN阶段驱动表的选择等。

文章转自数据和云公众号，