【中亦安图】SQL优化之基于SQL特征的改写(9)

  • 时间:
  • 浏览:1
  • 来源:神彩大发快3_彩神大发快3官方

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

>> 我们歌词 我们歌词 我们歌词 我们歌词 已经 的执行计划是那先 样的?(选取 优化目标)

4.4 增加冗余

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

1.2 SQL文本

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

前面我们歌词 我们歌词 我们歌词 我们歌词 不可能 分挥发性改写的关键点:改写后的话语不应该存在类事的最外层表涉及第二层子查询的情況;下面我们歌词 我们歌词 我们歌词 我们歌词 就朝着你什儿 目标去改写我们歌词 我们歌词 我们歌词 我们歌词 的SQL话语。

摆现象、列信息

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

>> 你什儿 执行计划算是为当前SQL话语下最优的执行计划?(选取 优化目标)

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

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

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

改写前信息补充:

不可能 可不都可不可不都可以,正在阅读此文的你,你说也可不都可不可不都可以思考一下后边的有一一十个 现象,不可能 回忆一下当你面对SQL tunning的现象时你有没办法 思考过这有一一十个 现象,亦不可能 已经 你思考/思考过那先 呢。

3.3 老K的答案----已经 的计划

....................................................................................................................................................

我们歌词 我们歌词 我们歌词 我们歌词 算是还记得原执行计划解析过程中老K给出的“访问公式”:

2.1 SQL文本价值形式

最终的执行计划:

修改过程的表访问=(还要修改的记录数 ×(S表全扫 + (0不可能 1次)×(S表全扫)))

本文来自于微信公众号转载文章,若有侵权,请联系小麦苗及时删除

Part 5

>> 针对CASE中的SQL何如通过去掉 索引来改善其执行下行速率

最终测试效果:

>> 没办法 改写后SQL执行过程中也会锁定还要修改的极少记录。

你什儿 改写依据的有几个关键点:

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

>> 不可能 select每段并能查到记录,则用原记录自身进行更新(set chq_pay_name=chq_pay_name),更新前后要记录的数据不变

>> TARGET_BIG_TABLE合适2G大小,SOURCE_SMALL_TABLE合适3M 大小;

在这里,我们歌词 我们歌词 我们歌词 我们歌词 看完了现象的关键,正是不可能 最外层的T表与两层子查询均有关联关系,意味ORACLE无法自动改写SQL,最终生成执行计划时无法使用T表与S表进行JOIN,并能生成使用filter依据的执行计划。

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

好了,今天老K与我们歌词 我们歌词 我们歌词 我们歌词 分享的案例是SQL调优的案例,但老K更希望我们歌词 我们歌词 我们歌词 我们歌词 能从中体会到SQL tunning过程中的优化依据和思维依据,真正做到它山之石,可不都可不可不都可以攻玉。同時 ,我们歌词 我们歌词 我们歌词 我们歌词 不可能 确实老K的依据还不错,不妨轻轻的转发一下,分享给身边更多的ORACLE技术爱好者。

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

话语如下:

没办法 改写会带来那先 坏处吗?

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

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

都也有!

所以,最终思考的结果不可能 出来:

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

4.3 继续改写

2.4 补充信息挂接之执行计划解读

2.3 补充信息挂接之表统计信息

不过针对你什儿 话语,我们歌词 我们歌词 我们歌词 我们歌词 从执行统计信息里知道,每次话语执行最终修改的数据量都非常少,也所以说没办法 改写所减少的“修改过程的表访问”对整体执行下行速率 影响不不大。

今天分析的现象是客户DBA给过来的三根SQL话语,不可能 困扰其一段时间了,希望老K同時 来分析处里。处里你什儿 现象对老K来说并也有很重难,不过在你什儿 现象的分析过程中,老K给出了几种优化的方向,最终选取 了不论是对整个系统还是对该条SQL都可谓最佳的有三种依据,最后在测试环境执行效果非常不错。

>> 要想生成较好的执行计划还要改写话语

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

最后的总览

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

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

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

1.4 执行统计信息

找价值形式、补信息

>> 持有行锁范围变大,不可能 多量意味某些对该表进行DML操作的会话被阻塞

>> 何如使用merge语法来改写update话语

我们歌词 我们歌词 我们歌词 我们歌词 再次回到SQL话语有三种,来看看SQL话语的很重之处。

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

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

原话语的part2每段修改的跟老K预期的差太满,原话语part1每段与part2每段一致,没办法 我们歌词 我们歌词 我们歌词 我们歌词 简单的修改part1每段成part2每段就可不都可不可不都可以啥已经 ?显然也有!通常,使用merge into话语能很方便的改写update话语,这里我们歌词 我们歌词 我们歌词 我们歌词 更能利用原话语part1和part2一致的价值形式,改写如下:

新的执行计划,老K又问了个人一句:

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

4.5 关键角色转变:

在测试环境,改写后的话语执行了两次,每次平均修改7.5条记录,耗时4s,逻辑读3.4w;细心的读者不可能 能从最终的执行计划中看完,对T表的全表扫描你说可不都可不可不都可以处里等,不可能 篇幅意味以及测试环境的意味,老K没办法 再在这里深究,毕竟老K分享的是SQL tuning的依据,而何如处里全表扫描以及何如分析处里了全表扫描后对SQL执行下行速率 提升的预估,相信读者你一定不可能 学到了,不妨个人做一有一一十个 估算。

中亦安图 | 2016-03-21 22:04

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

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

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

>> 我们歌词 我们歌词 我们歌词 我们歌词 为什么我么我么来让SQL跑出我们歌词 我们歌词 我们歌词 我们歌词 已经 的执行计划?(实现优化目标)

没办法 ,新建索引,将S表的全扫都变为索引扫描,这所以老K已经 的执行计划吗?

数据库 ORACLE 11.2.0.3 两节点RAC

其中最后某些,指出了我们歌词 我们歌词 我们歌词 我们歌词 改写的关键点。

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

>> 针对CASE中的SQL通过使用NVL的依据进行改写,它在那先 场景下是合适的,那先 情況下是不合适的。

老K先查看完该SQL的历史执行计划,并能你什儿 有一一十个 ,但这不不意味所以该SQL的最优执行计划;

在执行计划解读每段,老K给出了你什儿 执行计划的“访问公式”,从公式中可不都可不可不都可以知道确实S表虽小,但确实际上是整个执行计划的关键,整个过程中最多不可能 还要对S表进行1246K×2次访问呢,没办法 们可不可不都可不可不都可以提高对S表的访问下行速率 呢?当然可不都可不可不都可以,从执行计划中的估算可不都可不可不都可以知道对S表的访问合适返回1-2条记录(这里老K还单独验证过),说明整体选取 度比较高,我们歌词 我们歌词 我们歌词 我们歌词 并能创建合适的索引,就可不都可不可不都可以就可不都可不可不都可以大大将提高S表的访问下行速率 。

写在最后

....................................................................................................................................................

3.1 老K的例行思考

1.1 环境介绍

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

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

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

>> Merge的源与上一步改写的exists子句中的内容一致,所以把与a的关联关系提取到merge话语的on 每段;

不可能 主键a、d的主键列值相等,即可保证a、d的某些列值必然相等,所以a、d的关联字段只还要保留主键字段即可(保留也是可不都可不可不都可以的,去掉 显得更简洁)

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

>> 不可能 两层子查询的意味意味ORACLE无法使用JOIN的依据关联T表和S表

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

ITPUB BLOGhttp://blog.itpub.net/26736162

【版权所有,文章允许转载,但须以链接依据注明源地址,已经 追究法律责任】

>> 不可能 修改列上有索引,索引维护的时间将大大增加,意味新SQL执行下行速率 更低

思考吧DBA

执行计划出来完后 ,我们歌词 我们歌词 我们歌词 我们歌词 来估算一下你什儿 SQL在执行过程中的“访问公式”:

不过,不可能 原SQL在执行过程中修改的数据量接近623K条,没办法 你什儿 改写依据的收益就要高非常多,而其带来的坏处也就不复存在了,你什儿 改写依据所以不适合你什儿 业务环境下(每次只修改极少有几个记录),然而却有一定的普遍性,所以老K也把这每段分享给我们歌词 我们歌词 我们歌词 我们歌词 ,最重要的是处里现象过程中的思路和依据。

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

>> 何如通过去掉 冗余关联来引导数据库生成我们歌词 我们歌词 我们歌词 我们歌词 已经 的执行计划

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

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

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

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

4.6 减少冗余:

1.3 执行计划

Part 4

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

>> 不可能 select每段能查到记录(类事没办法 的exists子句成立),则用查询出的结果更新chq_pay_name字段

要回答你什儿 现象,我们歌词 我们歌词 我们歌词 我们歌词 首真难思考为那先 SQL当前没办法 跑出我们歌词 我们歌词 我们歌词 我们歌词 已经 的执行计划,是不可能 统计信息不准?索引设计不合理?还是列类型不匹配?

信息也有这了,我们歌词 我们歌词 我们歌词 我们歌词 要关注些那先 呢?老K的经验是,先找价值形式,再根据不同的价值形式来进一步提取个人还要的信息。

依据老K的经验,SQL话语的改写通常要求改写者对SQL涉及业务非常了解,通过业务价值形式重构出合理的SQL话语,并能更好的做到既不改变SQL的业务逻辑,又有效提高SQL性能;不过针对你什儿 SQL,我们歌词 我们歌词 我们歌词 我们歌词 不可能 知道了意味其执行计划不优的根本意味,老K相信可不都可不可不都可以在不考虑业务价值形式的情況,利用数据库的价值形式来进行有效的改写。

改写吧DBA

我们歌词 我们歌词 我们歌词 我们歌词 简单来估算一下使用索引的情況下的执行下行速率 是何如的。没办法 对S表全扫所需的逻辑读数为3M(表大小)÷8192=375次,使用索引后预估对S表一次访问最多所需逻辑读数为:(2次索引块访问 + 2次数据块访问)=4次;所以说,使用索引的逻辑读约为使用全扫的的1%,估算创建索引后要话语单次执行平均逻辑读约在360 w左右。

Part 1

About Me

>> 在各步骤单表访问依据均为全表扫描;

>> 不可能 d表和a表使用的是主键进行关联,所以能确保对a表的每条记录,都能从d中找到且并能找到三根记录符合话语中的关联关系;

4.7 别忘了”set“

操作系统 AIX 6.1

前言

4.1 改写的花絮

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

2.5 执行统计信息价值形式

今天老K继续与我们歌词 我们歌词 我们歌词 我们歌词 分享第九期。

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

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

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

3.2 老K的答案----也有最优的计划

2.2 执行计划的价值形式

Part 3

没办法 ,在你什儿 执行计划下,不可能 去掉 了冗余的一每段,公式就变成了:

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

QQ64260 8185 若加QQ请注明您所正在读的文章标题

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

读到了最后,老K分享了那先 ,我们歌词 我们歌词 我们歌词 我们歌词 不妨来仔细回忆一番。

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

>> 将话语改写为merge into的依据;

SQL文本他不知道们,确实SQL做的所以使用exists依据将T表和S表进行关联更新,老K已经 的执行计划应该是使用NL不可能 hash join的依据来连接两表,而也有使用filter迭代的依据,没办法 就能保证SQL执行过程中只还要对T表和S表进行极少的一次或有几个扫描,从而降低SQL执行的逻辑读。

4.2 没办法 改写真的好吗?

与原SQL执行计划类事,不过少了原执行计划的part1每段。

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

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

Part 2

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

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

>> 整个SQL话语中没办法 使用d表与某些表进行关联;

综上,针对这条SQL话语,你什儿 改写依据不不合适。

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

好了,信息挂接完成了,进入老K的既定思考轨道,确实对于任何一有一一十个 SQL tunning的现象,老K也有提出下面的有一一十个 现象,你什儿 所以用例外;

会!根本意味就在于后边提到的新SQL实际修改的记录数是623K条:

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