遇到变异版本的SAP优化难题,常规思路有用吗?

蒋健 2019-02-26 11:27:08
作者介绍

蒋健,云趣网络科技联合创始人,11g OCM,多年Oracle设计、管理及实施经验,精通数据库优化,Oracle CBO及并行原理。云趣鹰眼监控核心设计和开发者,资深Python Web开发者。

 

一、背景

 

某客户一个SAP月结CO模块出现问题,通过监控已经定位到了sql,但客户dba尝试优化了数次依然没好的效果(对于含in的SQL使用了use_concat hint),客户提了SR给SAP。SAP回复加上index hint,但是对执行计划依然没有影响,SQL性能问题拖了两天,客户DBA压力很大。

 

虽然只是其中一个环节的SQL,但由于该SQL性能低下,导致工厂计算成本计算不出来,而且月底用其他时候都不用,只在二月使用,所以对比该SQL历史执行计划。

 

二、问题初探

 

SQL本身其实很简单,只涉及三张表的连接,文本如下:

 

 

执行计划如下:

 

 

表的统计信息相对比较准确,执行计划中的性能瓶颈点很好定位,即红线划出的部分,在访问索引的时候,access方式访问了第一列的数据,然后获取全量数据再过滤。

 

比较明显第一列过滤后还有大量数据,当前执行计划中索引这种方式访问效率很低。问题比较明显,但解决起来方案没那么明显。

 

这是个看似眼熟的问题。

 

通常遇到in,or之类,CBO没有展开的情况下,是类似全表扫描带filter过滤,通常方案是加上use_concat的hint,配合合适的索引,就能解决问题。但这里,问题显然是个变异版本。那么接下来怎么处理呢?

 

三、处理思路

 

其实问题分析到这里,接下来的思路其实是比较多,简单列举下,如:

 

  • 排查索引聚促因子。看似合理,但一般用于走表不走索引的排查;

  • 进一步排查表,索引相关统计信息。重点可能需要排查直方图统计信息,或多列统计信息;

  • 改写SQL,写法上把in展开成or,但这里SQL本身不是特别复杂的SQL,其实改写有点怪怪的,特别在应用是SAP的背景下,改写了也不可能上线,所以不太合适做解决方案。但尝试改写了分析对比执行计划的outline部分,也是不错的思路;

  • 通过10053事件生成trace,分析这个异常执行计划的成因,再进一步做分析。这个适合没明显思路,难以理解CBO行为的时候,排查CBO的选择依据。

 

四、冷门hint

 

这里其实涉及到一个超低使用频率的冷门的hint:`num_index_keys`。如果客户在查看执行计划的时候注意观察下outline部分,就能看到这个hint。

 

关于这个hint 在oracle社区中也有过讨论,当时是有人生产环境升级到12c后有大量SQL执行计划变更,出现了大量的filter执行计划(并非bug)。

 

参考`"Access" and "Filter" changes when upgraded to 12c` 这个讨论:

https://community.oracle.com/thread/4184148

 

以及jonathanlewis的博客中也提到了这个hint。

 

这个hint指示优化器在进行“INLIST ITERATOR”操作时,可以使用多少个索引键来访问索引。下图为改后的hint,也就是将最末的位置从1改成了2:

 

 

改后的效果:直接访问前两列,四次扫描,不用获取第二列的全量数据,性能上大幅提升。

 

 

五、总结

 

对于数据的访问大部分情况下出现filter计划都是不好的,对于索引稍稍有点不同。

 

INLIST ITERATOR访问索引时,如果access完成后需要filter时的数据量很少,可能filter更好,而如果需要filter大量数据后,最终量比较少(也就条件的过滤性比较好,选择性比较高),则适合使用access继续访问接下来的数据。

 

使用Oracle社区的SQL改写了以下案例:

 

数据初始化

 

t1表中c1列只有两个值'0001','0002'。'0001'的选择性非常好,只需要通过c1='0001' 就能过滤出仅有的三条数据。

 

 

对比查询

 

数据库默认的选择就是对IDX1索引访问时使用access c1,c2列,filter c3列。

 

这里需要重点关注的有两个点:buffers 11,以及 `INDEX RANGE SCAN`访问索引IDX1时start了3次。

 

 

通过`num_index_keys`强制对IDX1索引的访问全部使用access。可以发现逻辑读buffers上升到了17,以及 `INDEX RANGE SCAN`访问索引IDX1时start了18次。

 

 

综上,对于索引的filter访问,如果作为SQL审核的考核点的话,有filter执行计划可被认为是有高风险的(尤其是执行频率高的SQL,access跟filter的执行计划cost差不多的情况),但依然需要具体场景进行人工验证。

活动预告