杀手SQL- 一条关于 'Not in' SQL 的优化案例

编辑手记:在 DBA 所优化的数据库环境中,绝大多数性能问题其实是由于 SQL 编写不当导致的。SQL 的世界无奇不有,今天我们一起见识一条让你绝对想吐血的杀手SQL。

某保险客户,ETL 耗时数个小时,我们做了sql report发现压力主要在其中一个SQL上。

单次执行时间:5788(秒)

单次逻辑读:10亿(块)

单次返回行数:21万(行)

我们首先看SQL语句,因为比较长,此处只节选部分的

查看其执行计划:

我们主要关注一下从7到16行:发现存在两次全表扫描。中间做了一次filter。

多年的经验告诉我,两个全表扫组成的Filter ,问题很严重, 因为涉及数据逐条处理。 而这个执行计划里,被驱动表还是全表扫。

Not In/In 操作有时候的确会产生 Filter操作,在11g之前的版本,要把not in 语句转换成反连接,not in条件的列必须有Not null 属性,或者语句中带入了not null的限制,否则只能采用Filter,逐条过滤.

我们举例说明一下:

SQL1:CREATE TABLE T_OBJ AS SELECT OBJECT_ID,OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OWNER != 'SEROL';

SQL2:CREATE TABLET_TABLE AS SELECT OWNER,TABLE_NAME FROM DBA_TABLES WHERE OWNER!='SEROL';

查看T_OBJ的属性:

发现有在三列上都没有not null的限制。

我们此时伪装成10G的优化器。

SQL> alter session set optimizer_features_enable="10.2.0.5";

执行以下SQL:

SQL> set autotracetrace exp SQL> SELECT * FROM T_TABLE WHERE TABLE_NAME NOT IN(SELECT OBJECT_NAME FROM T_OBJ);

此时查看执行计划,我们发现走的是filter:

但在11g版本中,优化器可以自动把Not in操作从昂贵的Filter转换成Null-Aware-Anti-Join。

若加个Not null 条件或者栏位属性设为not null

SQL> alter table T_OBJ modify(OBJECT_NAME NOT NULL);

再次执行相同语句:

SQL> SELECT * FROM T_TABLE WHERE TABLE_NAME NOT IN(SELECT OBJECT_NAME FROM T_OBJ WHEREOBJECT_NAME IS NOT NULL);

再次查看执行计划:

此时我们发现,在执行计划中,走了hash join anti.

并且,在11g里面,允许not in列没有not null 限制也可以转换Anti-Join.

SQL> alter session set optimizer_features_enable="11.2.0.4"; SQL> alter table T_OBJ modify(OBJECT_NAME NULL); SQ> SELECT * FROM T_TABLE WHERE TABLE_NAME NOT IN (SELECTOBJECT_NAMEFROM T_OBJ);

查看执行计划:

我们看到,此时在没有非空限制的情况下,也走了hash join anti.

这个特性, 可通过优化器参数控制。

SQL>alter session set "_optimizer_null_aware_antijoin"=FALSE;

再次执行以上语句并查看执行计划:

SQL> SELECT * FROM T_TABLE WHERE TABLE_NAME NOT IN (SELECTOBJECT_NAMEFROM T_OBJ);

发现仍然走的是hash join anti.

经过验证,不是这个参数设置问题

Not in 的逻辑,就是结果集之间的互斥,其实有多种改写的方式,比如:

-- Not exists

-- Outer Join + is null

-- Minus

not in与以上三种写法的区别是:not in 是会排斥空值。

我们尝试改写。

接下来正当你以为会发生奇迹的时候,语句报错了!

为什么会报错呢?

如果我们把该语句转换为not in的方式:

根据not in的逻辑,此时在fee_code前应该加上'A.',当然这也是没有问题的,但是,再次看这条语句就会变成:

由于TMP_APP_xxx_PREM A 中并没有FEE_CODE字段, 所以,Not in 无法自动改成Null Aware ANTI JOIN。

所以,至此答案揭晓,竟然是写错了?!我猜中了这开头,却没有猜中这结局。

但在本案例中,由于SQL语句中没有显式写出表明,导致在前期分析过程中一直没有发现这个错误。

你是不是也很无语,其实我更想问的是,你是不是也经常写出杀手SQL呢,但没关系,你有病我有药啊。(无辜脸,不要打我)

我们都知道,在 DBA 所优化的数据库环境中,绝大多数性能问题其实是由于 SQL 编写不当导致的。

SQL审核

SQL审核将 SQL 质量审核和优化这项任务,从 DB 端提取到研发端,通过擅长 SQL 的开发 DBA 和开发团队一起修正系统的 SQL,找出问题、修复问题,提升系统的健壮性和稳定性,从而保证整个系统的运维建设质量。

对于未上线系统,通过前期的SQL审核管控,将80%的SQL问题消灭在萌生阶段,对于线上运行系统,发现和解决潜在的性能问题,可做到提前预防,防患于未然。

SQL审核,让DBA由系统的急救医生转身成为系统的保健医生

1、DBA参与应用代码开发测试过程:给开发人员提供专业的数据库开发及优化建议 2、优化前置:在应用代码上线前根据业务需求设计高效的SQL、索引 3、控制变更风险:预先评估应用开发中表结构变更、SQL变更对运行中应用的影响,确定合适的变更窗口,变更方案。

SQL审核产品工具 - z3

云和恩墨基于Oracle数据库的SQL审核产品工具 - z3 ,通过内置的算法引擎,可定制的抓取数据库中执行的SQL及其详细数据,通过过滤分析,进行量化的积分趋势展现,并将SQL问题高亮显示,指导程序员的优化分析,并可以通过内置的工作流由DBA进行优化,变更管理同时被内置。

z3产品可服务于软件开发的全生命周期,在应用设计阶段通过审核,对不合理的表结构做变更,开发测试阶段对SQL质量严格把关,并在系统发布后进行SQL扫描,帮助DBA快速发现系统隐患及根源。

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

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

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏PPV课数据科学社区

SQL and R

R平台及编程语言支持浩大的数据科学技术,他拥有几十年的的历史和超过7000个包,这挂在CRAN的包纷杂的让你无法决定从哪里入手。R-Basics和Visua...

29710
来自专栏数据和云

量化的Oracle世界

Oracle数据库最为复杂的部分是优化器算法,在进行SQL解析的过程中,Oracle将一切都通过数字进行量化,然后评估比对,最后进行选择。在版本更新中,往往这部...

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

DBMS_STATS收集统计信息的问题及解决

收集数据库的统计信息是dba工作的一部分,如果在数据快速增长的库上,统计信息如果收集的频率太慢,会对执行计划有一定的影响。 而对于逐渐客户饱和的系统来说,统计信...

3174
来自专栏java架构学习交流

java 面试,java 后端面试,数据库方面对初级和高级程序员的要求

本内容摘自 java web轻量级开发面试教程 对于合格的程序员,需要有基本的数据库操作技能,具体体现在以下三个方面。 l  第一,针对一类数据库(比如MySQ...

1997
来自专栏一枝花算不算浪漫

[数据库设计]数据库设计三大范式

44112
来自专栏哎_小羊

私信基本功能数据库设计

上一篇写了基于resin4.0+websocket实现私信功能服务端消息推送文章,趁热打铁,在写一篇关于私信功能的数据库设计文章,非代码篇,希望想对第一次做设计...

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

Oracle分区数据问题的分析和修复

今天根据同事的反馈,处理了一个分区表的问题,也让我对Oracle的分区表功能有了进一步的理解。 首先根据开发同事的反馈,他们在程序批量插入一部分数据的时候...

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

关于奇怪的并行进程分析(二) (r6笔记第46天)

前几天的并行问题自己分析了下,也算有了一些进展,但是目前还没有找到让人信服的理由,有些读者也比较关心这个问题,所以第二篇中会把自己的分析过程写出来,第三篇中应该...

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

MySQL 5.7中锁的一个通用问题

前几天分析了一个死锁的问题,有一个网友看了以后,就发了邮件给我问一个问题。一般来说,能够发送邮件提出问题的同学,都是很认真的,因为他要准备好日志,准备好操作过...

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

一个oracle蠕虫病毒 (r4笔记第60天)

关于计算机病毒,说起来内容就很丰富了,但是第一次听到关于oracle中的病毒时,却感觉很新鲜。这是一个蠕虫病毒,距离现在已经有10年了,但是现在看起来还是能够借...

2363

扫描关注云+社区