SQL之美 - Oracle 子查询优化系列精讲

题记:SQL优化及SQL审核,是从源头解决性能问题的根本手段,无论是开发人员还是DBA,都应当持续深入的学习SQL开发技能,从而为解决性能问题打下根基。

本系列经典文章

之一:标量子查询优化

之二:OR展开与子查询优化案例详解。

今天是系列第三讲:IN子查询返回结果集异常

作者简介:

黄廷忠(网名:认真就输)

云和恩墨技术专家

个人博客:http://www.htz.pw/

正文

这是我们在一个客户现场遇到的一条SQL性能问题,此SQL子查询结果集返回最多10行,但是整个SQL的性能切不好,此SQL最后还导致了一个核心系统故障,引起了一个悲剧的事情。

业务反应慢,查询v$session发现同时有24个回话在执行此SQL:7ug8q9myb0bsz,由于此SQL性能不好引起大量的GC等待,导致其它的业务受影响。

下面直接给出常量的SQL

Child_number 0

Child_number 1

首先说明一下,是OLTP环境。也就以为着要快速的返回结果,并且多数情况下,SQL返回的结果集不多。

在上图SQL中,有两处我们用红色的箭头标识出来。说明这部分信息需要我们关注的。在整个SQL中,就只存在2处过滤信息,一个是redu_owner_id,一个是status_cd。但是status_cd在两个子执行计划中都是相同的,所以这里就只剩余redu_owner_id这列了,我们也可以执行redu_owner_id所在的OP这个表,肯定是驱动表,并且redu_owner_id这列应该存在数据倾斜的情况。那么redu_owner_id返回的结果集将直接影响整个SQL性能的好坏。

下面继续查看SQL部分,可以发现一个重要的信息就是在子查询中存在rownum<10,也就意味子查询最多返回10行。在OLTP系统中,存在一个表最后最多返回10行的情况,这里也就大概想到了用子查询做去驱动表了,如果执行计划中,没有用子查询做驱动表,那么很有肯能执行计划就是错误的,那么这里的自己认为的驱动表与之前根据SQL前部分猜测出来当前执行的驱动表(OP)不一样。

下面查看执行计划

在执行计划中,我们看到当前执行计划的驱动表示OFFER_PROD(OP)这个表,与之前我们猜想一样,那么基本可以肯定,redu_owner_id列的数据存在倾斜,当返回大量结果集时,性能就很不好。

在执行计划中,这里特意把子查询标记出来,就是需要引起重视,子查询当着一个整体与主查询做HASH链接,没有作为驱动表走NL,也就可以肯定整个执行计划连最基本的驱动表都选择错误。下图可以更直观的看到。

这里做个补充:子查询当作整体,也就是被当作一个视图与主机做关联,什么情况下子查询会当作一个整体呢?

其实MOS有相关的文档说明的,大家可以去MOS一下,在本案例是由于ROWNUM<10导致的。

在V$SQL中查看每个child的统计信息

这里看到,存在两个子游标,他们的执行计划相等,但是两个子优化的性能相差很大,并且性能不好的子优化执行次数很多。

在上面我们提到主查询就只存在两个过滤条件。执行计划+谓词信息可以看到驱动表使用那个列来过滤数据。

在上面一直在说redu_owner_id这个列存在数据倾斜,那么下面早证实一下:

下面来查看,redu_owner_id的值的分布。首先看看两个不同绑定变量返回的行数:

通过这个信息,我们知道了,上面SQL由于列的值存在倾斜,导致SQL执行计划部分值执行很快,部分值执行很慢。

大家可能会说,在11G中,SQL引入了ACS功能,但是很不幸的事在客户这里ACS都是禁用了的。

下面就是怎么来优化这个SQL。在上面提到了子查询中最多返回10行,可以用于做NL的驱动,要让子查询的表做驱动表,应该怎么来修改SQL?

当然我们也可以通过修改SQL为 with as 的方式,由于在这个系列的其他案例中使用过,因此我们换一种方式来实现。也就是通过提示来达到目的。

这里使用cardinality提示,在SQL解析的时候告诉CBO表上存在多少行。表上存在的行数越少,也就意味着访问表的成本越低。

下面我们拿返回8611行的绑定变量来做测试

添加提示后的SQL如下:

红色部分就是添加的提示

执行计划

可以看到,子查询的结果集已经作为驱动表了。

统计信息

每次的逻辑读从原来的369927降低到现在的45 ,性能提升很明显,并且主要解决了RAC之间的GC等待,不影响其它的业务了。

优化SQL后,CPU使用率从原来的70%左右直接下降到25%左右,此系统的主机性能很NB的,8路的PC ,E7的CPU。

此案例结束,主要提到两个知识点:

1, 列的值分布不均匀,导致SQL性能出问题。 2, 通过cardinality来指定表的行数,达到指定表做驱动表的目的。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-06-20

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏FreeBuf

经验分享:社会工程学数据库搭建TIPS

最近一直在搞社工库的搭建。网上这方面也有很多文章,但是很少涉及到细节,在此与大家分享一些个人心得。 测试环境 测试坏境:windows server 2012...

2348
来自专栏Hadoop数据仓库

HAWQ技术解析(十七) —— 最佳实践

一、HAWQ参数配置最佳实践 (原文地址:http://hawq.incubator.apache.org/docs/userguide/2.1.0.0-inc...

2057
来自专栏MySQL实战分享

数据库评测报告第一期:MySQL-5.7

这一期的评测报告就着重针对MySQL-5.7在SELECT和UPDATE 中的性能,通过与MariaDB-5.5的对比,展示下新版本MySQL的性能提升。

6794
来自专栏北京马哥教育

优化 SQL SELECT 语句性能的 6 个简单技巧

SELECT语句的性能调优有时是一个非常耗时的任务,在我看来它遵循帕累托原则。20%的努力很可能会给你带来80%的性能提升,而为了获得另外20%的性能提升你可能...

44511
来自专栏企鹅号快讯

WebGenerate 产品介绍

北京劳恩斯科技有限公司 网址: WebGenerate降本增效的代码生产工具,经过WebGenerate生产的代码适用于Spring、MyBatis、Maven...

1827
来自专栏码神联盟

碎片化 | 第四阶段-48-hibernate概述和配置-视频

本套视频从Java基础到架构模式以及AI算法,整体视频以“碎片化”学习的模式,提供给大家 ,并配备实际项目为案例,让大家在坐车、吃饭、午休、蹲坑的时候,都可以学...

3256
来自专栏美团技术团队

数据库运维必读的10个问题

本文中的问题精选自上期【你问我答】——数据库专题中读者的提问。【你问我答】是由美团点评技术团队推出的线上问答服务,你在工作学习中遇到的各种技术问题,都可以通过我...

35411
来自专栏杨建荣的学习笔记

物化视图自动刷新的碰壁(r7笔记第61天)

今天和开发的同事讨论一个问题,他们说source 1的环境中存在一个表,现在希望目标环境target 1和target 2中都需要用到这部分的数据。 ? 对...

3574
来自专栏张善友的专栏

SQLite vs MySQL vs PostgreSQL:关系型数据库比较

自1970年埃德加·科德提出关系模型之后,关系型数据库便开始出现,经过了40多年的演化,如今的关系型数据库种类繁多,功能强大,使用广泛。面对如此之多的关系型数据...

2285
来自专栏后端技术探索

当规模到亿级,MySQL是一个更好的NoSQL!

MySQL是一个更好的NoSQL数据库。当考虑到NoSQL的使用案例,比如对Key/Value键值存储来讲,MySQL在性能、易用性和稳定性方面更有意义。MyS...

1051

扫码关注云+社区