前言



今天老K继续与大家分享第九期。


周末老K宅在家观战了两局精彩的“人狗”大战。老K既算不上科技迷,也算不上围棋迷,不过对此颇有感触:阿尔法狗不过是通过左右互博的方式不断学习围棋,然而依赖其最优的学习算法(学习方法)却能再短短的数月之内达到人类围棋水平的最顶端;而李世石在却是依赖其已有的经验结合人类特有的灵感下出“神之一手”,人类终究还是可以战胜拥有超强计算能力的阿尔法狗。这些不禁让老K想起了自己在工作过程中的最有艺术性的部分---“SQL tunning”,一方面要不断学习积累运用不同的优化方法,同时在必要时多一分想象力和灵感,这样面对不同的SQL问题,我们才能下出自己的“神之一手”。


好了,今天老K与大家分享的案例是SQL调优的案例,但老K更希望大家能从中体会到SQL tunning过程中的优化方法和思维方式,真正做到它山之石,可以攻玉。同时,大家如果觉得老K的方法还不错,不妨轻轻的转发一下,分享给身边更多的ORACLE技术爱好者。

今天分析的问题是客户DBA给过来的一条SQL语句,已经困扰其一段时间了,希望老K一起来分析解决。解决这个问题对老K来说并不是特别难,不过在这个问题的分析过程中,老K给出了几种优化的方向,最终选择了不论是对整个系统还是对该条SQL都可谓最佳的一种方式,最后在测试环境执行效果非常不错。



Part 1

摆问题、列信息


对于SQL tunning,老K上手最先关注的是SQL文本、执行计划和执行统计信息,当然也不要忘了关注一下系统/数据库版本。


1.1 环境介绍


操作系统 AIX 6.1

数据库 ORACLE 11.2.0.3 两节点RAC


1.2 SQL文本

wKiom1l21fPzcRt6AAB7P7yjzhw691.png-wh_50

1.3 执行计划

wKiom1l21hKjeWCAAABdf9o-Ov0260.png-wh_50


1.4 执行统计信息

wKiom1l21h-SF6paAACAf2rKJeE377.png-wh_50


信息都在这了,我们要关注些什么呢?老K的经验是,先找特征,再根据不同的特征来进一步提取自己需要的信息。


Part 2

找特征、补信息

2.1 SQL文本特征


 >> exists子句 (part1)和update set部分(part2)的sql代码基本相同,如下图;

>> part1部分中,标量子查询的结果作为set列的目标值,说明从业务逻辑上能保证该部查询返回记录数最多为1;

wKioL1l21i-iHtDhAACXutrMzJ8097.png-wh_50

2.2 执行计划的特征

>> 该执行计划各过程均使用filter

>> 结合sql文本及predicate information可以看到,对目标表TARGET_BIG_TABLE经过滤条件POST_DATE=:V1后,返回记录数预估为623K条。

wKioL1l21j6AB9OHAAD2Au5b8Mw401.png-wh_50

2.3 补充信息收集之表统计信息


>> TARGET_BIG_TABLE大约2G大小,SOURCE_SMALL_TABLE大约3M 大小;

>> TARGET_BIG_TABLE表中记录数约250W左右,统计信息估算POST_DATE过滤后返回623K条记录,注意:这是预估值,实际值会随着传入的变量V1而变化。

>> SOURCE_SMALL_TABLE表中记录数约12W左右,ad02_acct_no列的选择度比较高;


2.4 补充信息收集之执行计划解读


注:TARGET_BIG_TABLE简称为T表    SOURCE_SMALL_TABLE 简称为S表

另注:解读关键----理解执行计划中的filter


>> 执行计划分开成两部分来看,其中ID2-7步表示对应SQL文本的part2部分,ID8-12步对应SQL文本的part1部分;


>> part2部分的过程:使用POST_DATE过滤T表,将过滤后的记录迭代入EXISTS子查询(T表的结果集此时作为变量传入子查询),在子查询执行的过程中,如果前面的关联条件符合,再次迭代入第二层子查询(select max()部分)进行匹配;


>> part1部分的过程:针对ID2-7步过滤出的结果集,逐条update,而update的目标值,同样是通过类似2-7步过程中的逐步迭代查询而来;


>> 在各步骤单表访问方式均为全表扫描;


>> 从执行计划中可以看到,在第3步对表T表进行过滤之后结果集估算为623K(rows列),其后对S表过滤后均为1;


>> 由此可以估算执行过程中表访问的情况应为:(老K建议在本分享中记住下面的公式,暂且称之为 “ 访问公式 ” 吧)


过滤过程的表访问=(T表全扫+  623K 次 ×(S表全扫  +(0或者 1次)×(S表全扫)))

修改过程的表访问=(需要修改的记录数  ×(S表全扫  + (0或者 1次)×(S表全扫)))

总的访问过程=过滤过程的表访问次数 +修改过程的表访问

注意:此处的(0或者 1次)×(S表全扫)表示的是第二层子查询的情况,如果在第一层子查询过程中关联条件就不符合,则不再需要迭代入第二层,即0次S表全扫,否则即是1次S表全扫;所以过滤过程对S表最少需要做623K次全扫,最多需要做1246K次全扫;修改过程同理。


2.5 执行统计信息特征


>> SQL单次执行平均逻辑读为355,245,774(block数)

>> SQL单次执行平均时间约2000秒

>> SQL单次平均修改记录数约为0条

wKioL1l21lSjO3kBAACQM3-gdQQ058.png-wh_50

Part 3

思考吧DBA


好了,信息收集完成了,进入老K的既定思考轨道,其实对于任何一个SQL tunning的问题,老K都会提出下面的三个问题,这个也不用例外;


3.1 老K的例行思考


>> 这个执行计划是否为当前SQL语句下最优的执行计划?(选择优化目标)

>> 我们想要的执行计划是什么样的?(确定优化目标)

>> 我们怎么来让SQL跑出我们想要的执行计划?(实现优化目标)


如果可以,正在阅读此文的你,也许也可以思考一下上面的三个问题,或者回忆一下当你面对SQL tunning的问题时你有没有思考过这三个问题,亦或者你会思考/思考过什么呢。

综合前期的分析思考片刻之后,老K郑重地给出了自己的答案:


3.2 老K的答案----不是最优的计划


老K先查看过该SQL的历史执行计划,只有这一个,但这并不意味着着就是该SQL的最优执行计划;


在执行计划解读部分,老K给出了这个执行计划的“访问公式”,从公式中可以知道其实S表虽小,但其实际上是整个执行计划的关键,整个过程中最多可能需要对S表进行1246K×2次访问呢,那我们可不可以提高对S表的访问效率呢?当然可以,从执行计划中的估算可以知道对S表的访问大约返回1-2条记录(这里老K还单独验证过),说明整体选择度比较高,我们只有创建合适的索引,就可以就可以大大将提高S表的访问效率。


我们简单来估算一下使用索引的情况下的执行效率是怎样的。原来对S表全扫所需的逻辑读数为3M(表大小)÷8192=375次,使用索引后预估对S表一次访问最多所需逻辑读数为:(2次索引块访问 + 2次数据块访问)=4次;所以说,使用索引的逻辑读约为使用全扫的的1%,估算创建索引后该语句单次执行平均逻辑读约在350w左右。


那么,新建索引,将S表的全扫都变为索引扫描,这就是老K想要的执行计划吗?

显然不是,这样的执行计划只是原执行计划的一个升级版而已,其过程还是一个迭代的过程,这样执行的时间/消耗的时间基本都会随着原计划中第3步返回的数据量(还记得623K这个值吗,就是它!它是可变的,可能随着传入的)变化而线性变化;所以这个执行计划虽然较原执行计划预计会有非常大的改善,但仍然不是老K想要的执行计划。


3.3 老K的答案----想要的计划


SQL文本告诉我们,其实SQL做的就是使用exists方式将T表和S表进行关联更新,老K想要的执行计划应该是使用NL或者hash join的方式来连接两表,而不是使用filter迭代的方式,这样就能保证SQL执行过程中只需要对T表和S表进行极少的一次或几次扫描,从而降低SQL执行的逻辑读。


3.4 老K的答案----如何生成漂亮的执行计划


要回答这个问题,我们首先要思考为什么SQL当前没有跑出我们想要的执行计划,是因为统计信息不准?索引设计不合理?还是列类型不匹配?

都不是!

我们再次回到SQL语句本身,来看看SQL语句的特别之处。

wKiom1l21m_hX6QYAABNqEWMkv0688.png-wh_50

在这里,我们看到了问题的关键,正是因为最外层的T表与两层子查询均有关联关系,导致ORACLE无法自动改写SQL,最终生成执行计划时无法使用T表与S表进行JOIN,只能生成使用filter方式的执行计划。


所以,最终思考的结果已经出来:

>> 因为两层子查询的原因导致ORACLE无法使用JOIN的方式关联T表和S表

>> 要想生成较好的执行计划必须改写语句

>> 改写后的语句不应该存在类似的最外层表涉及第二层子查询的情况

其中最后一点,指出了我们改写的关键点。


Part 4

改写吧DBA


依据老K的经验,SQL语句的改写通常要求改写者对SQL涉及业务非常了解,通过业务特征重构出合理的SQL语句,才能更好的做到既不改变SQL的业务逻辑,又有效提高SQL性能;不过针对这个SQL,我们已经知道了导致其执行计划不优的根本原因,老K相信可以在不考虑业务特性的情况,利用数据库的特性来进行有效的改写。


4.1 改写的花絮


基于SQL特性中,part1和part2基本相同的特性,老K先随性的对SQL做了如下改写(当然没有针对前面提到的改写关键点);

wKiom1l21oHyHh38AABlYhlRQvo758.png-wh_50

这一改写方式的几个关键点:

>> 先把post_date字段的过滤条件直接提取出来,与原逻辑一致

>> 基于part1和part2基本相同,使用了nvl函数代替了原来的exists子句

>> 如果select部分能查到记录(类似原来的exists子句成立),则用查询出的结果更新chq_pay_name字段

>> 如果select部分不能查到记录,则用原记录自身进行更新(set chq_pay_name=chq_pay_name),更新前后该记录的数据不变


以上几点保证了改写后的SQL与原SQL逻辑一致,不过有一点不一样的非常值得注意,原SQL只修改极少的几条记录,新SQL却修改了623K条记录,只是其中绝大多数是冗余的修改。


我们再看改写后的SQL执行计划:

wKioL1l21pbxPIb_AABP3cBGja0492.png-wh_50

与原SQL执行计划类似,不过少了原执行计划的part1部分。

新的执行计划,老K又问了自己一句:


4.2 这样改写真的好吗?


大家是否还记得原执行计划解析过程中老K给出的“访问公式”:

总的访问过程=过滤过程的表访问次数 +修改过程的表访问

那么,在这个执行计划下,因为去掉了冗余的一部分,公式就变成了:

总的访问过程=过滤过程的表访问次数


实际上就可以理解为,SQL在修改数据的过程中可以重用过滤过程中生成的数据;

不过针对这个语句,我们从执行统计信息里知道,每次语句执行最终修改的数据量都非常少,也就是说这样改写所减少的“修改过程的表访问”对整体执行效率影响并不大。


这样改写会带来什么坏处吗?

会!根本原因就在于上面提到的新SQL实际修改的记录数是623K条:


>> 持有行锁范围变大,可能大量导致其他对该表进行DML操作的会话被阻塞

>> 如果修改列上有索引,索引维护的时间将大大增加,导致新SQL执行效率更低


综上,针对这条SQL语句,这种改写方式并不合适。

不过,如果原SQL在执行过程中修改的数据量接近623K条,那么这种改写方式的收益就要高非常多,而其带来的坏处也就不复存在了,这种改写方式只是不适合这种业务环境下(每次只修改极少几条记录),然而却有一定的普遍性,所以老K也把这部分分享给大家,最重要的是解决问题过程中的思路和方法。


4.3 继续改写


前面我们已经分析出改写的关键点:改写后的语句不应该存在类似的最外层表涉及第二层子查询的情况;下面我们就朝着这个目标去改写我们的SQL语句。


改写前信息补充:

改写思路在老K脑中酝酿好后,老K又补查了T表的信息,确认T表存在主键约束,主键列为ACCT_NO和JRNL_NO;


4.4 增加冗余

wKioL1l21qnipKsmAACMhbsbMXM735.png-wh_50

>> 在exists子句中增加一个冗余的T表,别名为d

>> 增加d表和a表的关联关系,其中jrnl_no列和acct_no列组合为T表的主键,其他冗余列的关联主要为下一步继续的改写作铺垫;

>> 整个SQL语句中没有使用d表与其他表进行关联;

>> 由于d表和a表使用的是主键进行关联,所以能确保对a表的每条记录,都能从d中找到且只能找到一条记录符合语句中的关联关系;


综上,可以知道上述增加冗余完全不改变SQL的逻辑关系。


4.5 关键角色转变:


wKioL1l21rOCBPj9AACIkqJtqFI482.png-wh_50

基于第一步冗余等价关系,将exists子句中的所有a与b、c的关联关系替换为d与b、c的关联关系。


4.6 减少冗余:


wKiom1l21rzjCCEfAABugXViISM621.png-wh_50

因为主键a、d的主键列值相等,即可保证a、d的其他列值必然相等,所以a、d的关联字段只需要保留主键字段即可(保留也是可以的,去掉显得更简洁)


以上一步一步的改写保证了逻辑的一致性,同时实现了最外层的T表不再涉及第二层子查询的关联,我们可以推断执行计划应该与老K预期的相差不远了:


wKioL1l21sXCA048AADWaWRWoRU861.png-wh_50

>> 执行计划中b、d、c表使用hash join进行关联

>> join完成后通过一系列SORT/FILTER后形成结果集VW_SQ_2,其中这里的filter部分为结果集内部的比较(即同一条记录的不同列的比较),效率非常高

>> 最后VW_SQ_2和外层的T表使用NL的方式进行join,关联字段为主键字段


执行计划出来以后,我们来估算一下这个SQL在执行过程中的“访问公式”:

总的访问过程 = S表全扫 + T表全扫 + S表全扫 + VW_SQ_2记录数 *(1个T表主键索引块 + 1个T表数据块)


4.7 别忘了”set“


原语句的part2部分修改的跟老K预期的差不多,原语句part1部分与part2部分一致,那么我们简单的修改part1部分成part2部分就可以了吗?显然不是!通常,使用merge into语句能很方便的改写update语句,这里我们更能利用原语句part1和part2一致的特性,改写如下:


wKiom1l21s-CtlFbAAB0ByK6D9o515.png-wh_50

>> 将语句改写为merge into的方式;

>> Merge的源与上一步改写的exists子句中的内容一致,只是把与a的关联关系提取到merge语句的on 部分;

>> 这样改写后SQL执行过程中也会锁定需要修改的极少记录。


这里改写后的执行计划与前面的update语句类似,老K也就不单独列出分析了。


Part 5

最后的总览


最后我们再来看看我们改写后的语句及其执行计划:

语句如下:


wKioL1l21tijN9PmAABVeuHAhY8781.png-wh_50

最终的执行计划:


wKiom1l21uGCam_pAABijZadmxA551.png-wh_50

最终测试效果:


wKioL1l21uqQo7RpAABXWUCuwWA542.png-wh_50

在测试环境,改写后的语句执行了两次,每次平均修改7.5条记录,耗时4s,逻辑读3.4w;细心的读者可能能从最终的执行计划中看到,对T表的全表扫描也许可以避免等,由于篇幅原因以及测试环境的原因,老K没有再在这里深究,毕竟老K分享的是SQL tuning的方法,而如何避免全表扫描以及如何分析避免了全表扫描后对SQL执行效率提升的预估,相信读者你一定已经学到了,不妨自己做一个估算。


写在最后



读到了最后,老K分享了什么,我们不妨来仔细回忆一番。


>> SQL分析过程中如何通过执行计划推算SQL执行的逻辑读

>> 针对CASE中的SQL如何通过添加索引来改善其执行效率

>> 针对CASE中的SQL通过使用NVL的方式进行改写,它在什么场景下是合适的,什么情况下是不合适的。

>> 怎样通过添加冗余关联来引导数据库生成我们想要的执行计划

>> 怎样使用merge语法来改写update语句


最后,老K再一次强调,在SQLtunning的过程中最重要的是优化的思路和对问题的思考方式,希望聪明的读者已从这次分享中得到启示。

编外:老K后来通过与应用开发团队沟通了解文中SQL的业务特征后,再次结合其业务特征改写了SQL,执行效率再次得到了极大的提升,可见,在SQLtunning的过程中,了解业务确实是非常重要的一环。