见微知著:一条 SQL 性能问题引发的核心系统悲剧

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

云和恩墨技术专家

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

本篇整理内容是黄廷忠在“云和恩墨大讲堂”微信分享中的讲解案例,SQL 优化及 SQL审核,是从源头解决性能问题的根本手段,无论是开发人员还是DBA,都应当持续深入的学习 SQL 开发技能,从而为解决性能问题打下根基。

第一篇为:性能为王:SQL标量子查询的优化案例分析

第二篇为:SQL审核:OR展开与子查询优化案例详解

本篇为系列案例之三:IN子查询返回结果集很小

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

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

SQL性能问题诊断

下面直接给出常量的 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 的修改

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

在上一个案例中,通过了with as 的方式来改写。 这个案例就不修改 SQL,通过提示(Hints)来达到目的。

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

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

添加提示后的 SQL 如下:

红色部分就是添加的提示,执行计划如下:

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

性能优化效果对比

统计信息

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

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

总结

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

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

SQL优化是一项专业的技能,必须深入了解数据库原理才可能做出准确的判断,在开发过程中强化开发人员培训,引入SQL审核、审计,是确保应用高性能的有力手段。

近期文章

新年贺礼:云和恩墨大讲堂期刊第二期

删繁就简-云和恩墨的一道面试题解析

用SQL解一道数学题:Gauss和Poincare

新年贺礼:云和恩墨大讲堂期刊发行

2015 Oracle 十大热门文章精选

Oracle 12c ASM 防火防盗新特性揭秘

DBA入门之路:学习与进阶之经验谈

DBA入门之路:关于日常工作的建议

业务架构

电子渠道(网络销售)分析系统、数据治理

IT基础架构

分布式存储解决方案 | zData一体机 | 容灾环境建设

数据架构

Oracle DB2 MySQL NoSQL

专项服务:架构/安全/容灾/优化/整合/升级/迁移

运维服务:运维服务 代维服务

人才培养:个人认证 企业内训

软件产品:SQL审核、监控、数据恢复

应用架构

应用软件和中间件:数据建模 | SQL审核和优化 | 中间件服务

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

原文发表时间:2016-02-29

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏华章科技

520特别版Python实战:教你用微信每天给TA说晚安

from __future__ import unicode_literals from threading import Timer from wxpy i...

1021
来自专栏张善友的专栏

尝试用微博记录 SQL Server 2012开发者训练营笔记

花了2天时间参加微软的SQL Server 2012开发者训练营,全面的学习了SQL Server 2012上面的新特性,尝试使用微博做笔记。现在把它摘录到博客...

1909
来自专栏代码世界

初识数据库

一、数据库概念 数据库(Database,简称DB)   数据库技术是计算机应用领域中非常重要的技术,它产生于20世纪60年代末,是数据管理的最新技术,也是软件...

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

性能下降的不定时炸弹_过旧的sql_profile(r3笔记第9天)

最近这一周以来,生产环境像是得了重病的病人一样,小问题没有修好,大问题不断。IO的等待极为严重。数据库的负载达到了几十倍,上百倍。 weblogic和tuxed...

3307
来自专栏数据和云

故障树分析法在数据库诊断分析中的应用

编辑手记:将知识转化为能力,除了需要经验的积累和时间的磨砺,更重要的是正确的方法和思维模式,学会应用知识才是真正的能力。本文试图通过方法的讨论使大家能够形成一个...

46214
来自专栏华章科技

【一文打尽】SQL 数据分析常用语句.....收藏

• 1 基础查询 • 2 字符串\数字\日期时间 • 3 聚合数据查询 • 4 子查询 • 5 联接\组合查询 • 6 高级查询 • 7 更新数据

601
来自专栏数据和云

深入剖析:not exists对外层查询的影响

何剑敏 Oracle ACS华南区售后团队,首席技术工程师。供职于Oracle ACS华南区售后团队,首席技术工程师。多年从事第一线的数据库运维工作,有丰富项...

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

数据同步中的误导(r7笔记第34天)

今天同事让我帮一个忙,说现在有两个环境中的一张表数据不一致,已经造成了一些数据问题,他们已经排查了一圈,最后发现是一张表的数据问题导致,希望我来帮忙协助一下。 ...

3498
来自专栏MySQL实战分享

数据库评测报告第二期:MongoDB-3.2

看到MongoDB如此特性和优势,不免勾起了我们的好奇心。这一期的评测报告就着重针对MongoDB的读写性能的进行测试和分析,一起来揭秘一下如今MongoDB在...

6252
来自专栏一名叫大蕉的程序员

慢SQL,压垮团队的最后一根稻草No.92

先说结论,我支持将逻辑写在 Java 等应用系统中。 背景:今天只讨论一种应用模式,就是最普遍的,前端实时调用后端web服务,服务端经过DB的增删改查作出响应的...

4056

扫码关注云+社区