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 条评论
登录 后参与评论

相关文章

来自专栏北京马哥教育

Redis集群服务器-高可用调研随笔

一、Redis与MySQL对比 相同点: Master-Slave架构,集群架构下无法很好的完成数据拷贝,确保数据一致性。 支持数据文件持久化存储,但数据文件...

3035
来自专栏乐沙弥的世界

Oracle DB Time 解读

Oracle DB Time是Oracle数据库在时间维度上剖析性能的一个重要指标,通过逐级分解该指标,定位到浪费资源或者资源争用的首要事件上,从而通过减少等待...

241
来自专栏程序员互动联盟

【编程基础】什么是内存泄露

内存泄漏也称作“存储渗漏”,用动态存储分配函数动态开辟的空间,在使用完毕后未释放,结果导致一直占据该内存单元。直到程序结束。(其实说白了就是该内存空间使用完毕之...

3346
来自专栏大内老A

谈谈分布式事务之四: 两种事务处理协议OleTx与WS-AT

在年前写一个几篇关于分布式事务的文章,实际上这些都是为了系统介绍WCF事务处理体系而提供的相关的背景和基础知识。今天发最后一篇,介绍分布式事务采用的两种协议,即...

1818
来自专栏CSDN技术头条

NoSQL数据库的主主备份

Tarantool DBMS的高性能应该很多人都听说过,包括其丰富的工具套件和某些特定功能。比如,它拥有一个非常强大的on-disk存储引擎Vinyl,并且知道...

19010
来自专栏刘君君

Rest Notes-REST架构的元素

1265
来自专栏Jacklin攻城狮

翻译_iOS视图编程指南(View Programming Guide for iOS)之视图和窗口体系

前些日子,我发布一个苹果官方文档的翻译,之后就有不少同学朋友问我:翻译苹果官方文档能做什么,开发过程用到的时候很少,浪费时间,还又没什么用。今天,刚好有时间,就...

634
来自专栏ImportSource

设计模式-搞个接口,留有余地,让你我不再尴尬

设计模式,Design Patterns,Pattern,翻译为“模式”总感觉不够接地气,用今天的话来说可以叫“套路”。设计模式就是写代码的过程中一些常规打法和...

33611
来自专栏简书专栏

mysql必知必会

数据库是按照数据结构来组织、存储和管理数据的仓库,它产生于距今。 六十多年前,随着信息技术和市场的发展,特别是二十世纪九十年代以后,数据管理不再仅仅是存储和管...

241
来自专栏linux驱动个人学习

CPUFreq驱动

CPUFreq子系统位于 drivers/cpufreq目录下,负责进行运行过程中CPU频率和电压的动态调整,即DvFS( Dynamic Voltage Fr...

743

扫描关注云+社区