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

  • 时间:
  • 浏览:0
  • 来源:万人牛牛_万人牛牛官网

>> 针对CASE中的SQL通过使用NVL的土办法进行改写,它在哪此场景下是大约 的,哪此情形下是不大约 的。

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

4.7 别忘了”set“

>> 这名执行计划是是否为当前SQL一段话下最优的执行计划?(取舍优化目标)

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

>> 机会两层子查询的因为 因为 ORACLE无法使用JOIN的土办法关联T表和S表

操作系统 AIX 6.1

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

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

>> 亲戚亲戚我们我们我们我们我们我们让你的执行计划是哪此样的?(取舍优化目标)

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

显然是否,原来的执行计划太少太少太少太少 原执行计划的有另三个升级版而已,其过程还是有另三个迭代的过程,原来执行的时间/消耗的时间基本一定会随着原计划中第3步返回的数据量(还记得623K这名值吗,太少太少太少太少 它!它是可变的,机会随着传入的)变化而线性变化;太少太少太少太少这名执行计划不言而喻较原执行计划预计会有非常大的改善,但仍然是否老K让你的执行计划。

亲戚亲戚我们我们我们我们我们我们简单来估算一下使用索引的情形下的执行带宽是如保的。原来对S表全扫所需的逻辑读数为3M(表大小)÷8192=375次,使用索引后预估对S表一次访问最多所需逻辑读数为:(2次索引块访问 + 2次数据块访问)=4次;太少太少太少太少说,使用索引的逻辑读约为使用全扫的的1%,估算创建索引后会一段话单次执行平均逻辑读约在3500w左右。

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

要回答这名哪此的问题报告 ,亲戚亲戚我们我们我们我们我们我们首比较慢思考为哪此SQL当前这么跑出亲戚亲戚我们我们我们我们我们我们让你的执行计划,是机会统计信息不准?索引设计不合理?还是列类型不匹配?

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

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

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

>> Merge的源与上一步改写的exists子句中的内容一致,太少太少太少太少 把与a的关联关系提取到merge一段话的on 次责;

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

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

>> 如保使用merge语法来改写update一段话

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

注意:此处的(0机会 1次)×(S表全扫)表示的是第二层子查询的情形,机会在第一层子查询过程中关联条件就不符合,则不再还要迭代入第二层,即0次S表全扫,后来即是1次S表全扫;太少太少太少太少过滤过程对S表大约 还要做623K次全扫,最多还要做1246K次全扫;修改过程同理。

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

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

数据库 ORACLE 11.2.0.3 两节点RAC

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

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

【版权所有,文章允许转载,但须以链接土办法注明源地址,后来追究法律责任】

写在最后

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

好了,信息分发完成了,进入老K的既定思考轨道,不言而喻对于任何有另三个SQL tunning的哪此的问题报告 ,老K一定会提出下面的有另三个哪此的问题报告 ,这名太少太少太少太少 用例外;

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

Part 1

原一段话的part2次责修改的跟老K预期的差太少,原一段话part1次责与part2次责一致,这么亲戚亲戚我们我们我们我们我们我们简单的修改part1次责成part2次责就可不还要啥后来?显然是否!通常,使用merge into一段话能很方便的改写update一段话,这里亲戚亲戚我们我们我们我们我们我们更能利用原一段话part1和part2一致的特征,改写如下:

4.1 改写的花絮

3.1 老K的例行思考

找特征、补信息

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

>> 在各步骤单表访问土办法均为全表扫描;

4.2 原来改写真的好吗?

Part 4

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

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

机会可不还要,正在阅读此文的你,是我不好也可不还要思考一下底下的有另三个哪此的问题报告 ,机会回忆一下当你面对SQL tunning的哪此的问题报告 时你有这么思考过这有另三个哪此的问题报告 ,亦机会我就思考/思考过哪此呢。

4.3 继续改写

在这里,亲戚亲戚我们我们我们我们我们我们想看 了哪此的问题报告 的关键,正是机会最外层的T表与两层子查询均有关联关系,因为 ORACLE无法自动改写SQL,最终生成执行计划时无法使用T表与S表进行JOIN,这么生成使用filter土办法的执行计划。

好了,今天老K与亲戚亲戚我们我们我们我们我们我们分享的案例是SQL调优的案例,但老K更希望亲戚亲戚我们我们我们我们我们我们能从中体会到SQL tunning过程中的优化土办法和思维土办法,真正做到它山之石,可不还要攻玉。一并,亲戚亲戚我们我们我们我们我们我们机会不言而喻老K的土办法还不错,不妨轻轻的转发一下,分享给身边更多的ORACLE技术爱好者。

>> 针对CASE中的SQL如保通过加进索引来改善其执行带宽

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

原来改写会带来哪此坏处吗?

About Me

不过,机会原SQL在执行过程中修改的数据量接近623K条,这么这名改写土办法的收益就要高非常多,而其带来的坏处也就不复处在了,这名改写土办法太少太少太少太少 不适合这名业务环境下(每次只修改极少十几个 记录),然而却有一定的普遍性,太少太少太少太少老K也把这次责分享给亲戚亲戚我们我们我们我们我们我们,最重要的是除理哪此的问题报告 过程中的思路和土办法。

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

2.4 补充信息分发之执行计划解读

不过针对这名一段话,亲戚亲戚我们我们我们我们我们我们从执行统计信息里知道,每次一段话执行最终修改的数据量都非常少,也太少太少太少太少 说原来改写所减少的“修改过程的表访问”对整体执行带宽影响不言而喻大。

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

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

3.3 老K的答案----让你的计划

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

最后的总览

今天老K继续与亲戚亲戚我们我们我们我们我们我们分享第九期。

2.3 补充信息分发之表统计信息

信息是否这了,亲戚亲戚我们我们我们我们我们还要关注些哪此呢?老K的经验是,先找特征,再根据不同的特征来进一步提取买车人还要的信息。

Part 5

老K先查想看 该SQL的历史执行计划,这么这有另三个,但这不言而喻因为 着太少太少太少太少 该SQL的最优执行计划;

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

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

>> 将一段话改写为merge into的土办法;

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

太少太少太少太少,最终思考的结果机会出来:

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

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

最终的执行计划:

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

1.3 执行计划

最后亲戚亲戚我们我们我们我们我们我们再来看看亲戚亲戚我们我们我们我们我们我们改写后的一段话及其执行计划:

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

>> 整个SQL一段话中这么使用d表与太少太少表进行关联;

2.1 SQL文本特征

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

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

改写前信息补充:

>> 改写后的一段话不应该处在类似于 的最外层表涉及第二层子查询的情形

>> 亲戚亲戚我们我们我们我们我们我们缘何来让SQL跑出亲戚亲戚我们我们我们我们我们我们让你的执行计划?(实现优化目标)

综上,可不还要知道上述增加冗余删剪不改变SQL的逻辑关系。

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

思考吧DBA

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

与原SQL执行计划类似于 ,不过少了原执行计划的part1次责。

亲戚亲戚我们我们我们我们我们我们再次回到SQL一段话一种,来看看SQL一段话的有点儿之处。

>> 机会select次责能查到记录(类似于 原来的exists子句成立),则用查询出的结果更新chq_pay_name字段

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

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

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

是是否!

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

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

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

前言

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

前面亲戚亲戚我们我们我们我们我们我们机会分溶解改写的关键点:改写后的一段话不应该处在类似于 的最外层表涉及第二层子查询的情形;下面亲戚亲戚我们我们我们我们我们我们就朝着这名目标去改写亲戚亲戚我们我们我们我们我们我们的SQL一段话。

>> 机会select次责这么查到记录,则用原记录自身进行更新(set chq_pay_name=chq_pay_name),更新前后会记录的数据不变

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

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

Part 2

SQL文本谁能告诉亲戚亲戚我们我们我们我们我们我们,不言而喻SQL做的太少太少太少太少 使用exists土办法将T表和S表进行关联更新,老K让你的执行计划应该是使用NL机会hash join的土办法来连接两表,也不 是否使用filter迭代的土办法,原来就能保证SQL执行过程中只还要对T表和S表进行极少的一次或十几个 扫描,从而降低SQL执行的逻辑读。

这名改写土办法的十几个 关键点:

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

2.5 执行统计信息特征

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

读到了最后,老K分享了哪此,亲戚亲戚我们我们我们我们我们我们不妨来仔细回忆一番。

>> 持有行锁范围变大,机会血块因为 太少太少对该表进行DML操作的会话被阻塞

摆哪此的问题报告 、列信息

改写吧DBA

今天分析的哪此的问题报告 是客户DBA给过来的两根SQL一段话,机会困扰其一段时间了,希望老K一并来分析除理。除理这名哪此的问题报告 对老K来说不言而喻是有点儿难,不过在这名哪此的问题报告 的分析过程中,老K给出了几种优化的方向,最终取舍了不论是对整个系统还是对该条SQL都可谓最佳的一种土办法,最后在测试环境执行效果非常不错。

综上,针对这条SQL一段话,这名改写土办法不言而喻大约 。

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

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

1.4 执行统计信息

机会主键a、d的主键列值相等,即可保证a、d的太少太少列值必然相等,太少太少太少太少a、d的关联字段只还要保留主键字段即可(保留也是可不还要的,加进显得更简洁)

一段话如下:

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

>> 机会d表和a表使用的是主键进行关联,太少太少太少太少能确保对a表的每条记录,都能从d中找到且这么找到两根记录符合一段话中的关联关系;

4.6 减少冗余:

2.2 执行计划的特征

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

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

1.1 环境介绍

>> 如保通过加进冗余关联来引导数据库生成亲戚亲戚我们我们我们我们我们我们让你的执行计划

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

其中最后太少太少,指出了亲戚亲戚我们我们我们我们我们我们改写的关键点。

这么,新建索引,将S表的全扫都变为索引扫描,这太少太少太少太少 老K让你的执行计划吗?

最终测试效果:

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

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

4.5 关键角色转变:

土办法老K的经验,SQL一段话的改写通常要求改写者对SQL涉及业务非常了解,通过业务特征重构出合理的SQL一段话,并能更好的做到既不改变SQL的业务逻辑,又有效提高SQL性能;不过针对这名SQL,亲戚亲戚我们我们我们我们我们我们机会知道了因为 其执行计划不优的根本因为 ,老K相信可不还要在不考虑业务特征的情形,利用数据库的特征来进行有效的改写。

>> 机会修改列上有索引,索引维护的时间将大大增加,因为 新SQL执行带宽更低

Part 3

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

这么,在这名执行计划下,机会加进了冗余的一次责,公式就变成了:

4.4 增加冗余

在执行计划解读次责,老K给出了这名执行计划的“访问公式”,从公式中可不还要知道不言而喻S表虽小,但不言而喻际上是整个执行计划的关键,整个过程中最多机会还要对S表进行1246K×2次访问呢,原来们可不可不还要提高对S表的访问带宽呢?当然可不还要,从执行计划中的估算可不还要知道对S表的访问大约 返回1-2条记录(这里老K还单独验证过),说明整体取舍度比较高,亲戚亲戚我们我们我们我们我们我们这么创建大约 的索引,就可不还要就可不还要大大将提高S表的访问带宽。

在测试环境,改写后的一段话执行了两次,每次平均修改7.5条记录,耗时4s,逻辑读3.4w;细心的读者机会能从最终的执行计划中想看 ,对T表的全表扫描是我不好可不还要除理等,机会篇幅因为 以及测试环境的因为 ,老K这么再在这里深究,毕竟老K分享的是SQL tuning的土办法,而如保除理全表扫描以及如保分析除理了全表扫描后对SQL执行带宽提升的预估,相信读者你一定机会学到了,不妨买车人做有另三个估算。

1.2 SQL文本

>> join完成后通过一系列SORT/FILTER后形成结果集VW_SQ_2,其中这里的filter次责为结果集内内外部的比较(即同两根记录的不同列的比较),带宽非常高