专栏首页数据和云SQL优化:紧急情况下提高SQL性能竟是这样实现的!

SQL优化:紧急情况下提高SQL性能竟是这样实现的!

作者 | 黄堋 ,多年一线 Oracle DBA 经验,长期服务电信、电网、医院、政府等行业客户。擅长数据库优化、数据库迁移升级、数据库故障处理。

在某运营商的优化经历中曾经遇到了一条比较有意思的 SQL,具体如下:

1 该最开始的 sql 执行情况如下

SQL> SELECT 2 NVL(T.RELA_OFFER_SPEC_ID, SUBOS.SUB_OFFER_SPEC_ID) "offerSpecId" 3 FROM OFFER_SPEC_RELA T 4 LEFT JOIN OFFER_SPEC_GRP_RELA SUBOS 5 ON T.RELA_GRP_ID = SUBOS.OFFER_SPEC_GRP_ID 6 AND subos.start_dt <= SYSDATE 7 AND subos.end_dt >= SYSDATE 8 WHERE T.RELA_TYPE_CD = 2 9 AND t.start_dt <= SYSDATE 10 AND t.end_dt >= SYSDATE 11 AND (T.OFFER_SPEC_ID = 109910000618 12 OR EXISTS 13 (SELECT A.OFFER_SPEC_GRP_ID 14 FROM OFFER_SPEC_GRP_RELA A 15 WHERE A.SUB_OFFER_SPEC_ID = 109910000618 16 AND T.OFFER_SPEC_GRP_ID = A.OFFER_SPEC_GRP_ID 17 )) 18 AND rownum<500; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 1350156609

Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<500) 2 - filter("T"."OFFER_SPEC_ID"=109910000618 OR EXISTS (SELECT 0 FROM "SPEC"."OFFER_SPEC_GRP_RELA" "A" WHERE "A"."OFFER_SPEC_GRP_ID"=:B1 AND "A"."SUB_OFFER_SPEC_ID"=109910000618)) 3 - access("T"."RELA_GRP_ID"="SUBOS"."OFFER_SPEC_GRP_ID"(+)) 4 - filter("T"."RELA_TYPE_CD"=2 AND "T"."END_DT">=SYSDATE@! AND "T"."START_DT"<=SYSDATE@!) 5 - filter("SUBOS"."END_DT"(+)>=SYSDATE@! AND "SUBOS"."START_DT"(+)<=SYSDATE@!) 6 - access("A"."SUB_OFFER_SPEC_ID"=109910000618 AND "A"."OFFER_SPEC_GRP_ID"=:B1) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 12444 consistent gets 0 physical reads 0 redo size 339 bytes sent via SQL*Net to client 509 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed PLAN GET DISK WRITE ROWS ROWS USER_IO(MS) ELA(MS) CPU(MS) CLUSTER(MS) PLSQL END_TI I HASH VALUE EXEC PRE EXEC PRE EXEC PER EXEC ROW_P PRE EXEC PRE FETCH PER EXEC PRE EXEC PRE EXEC PER EXEC PER EXEC

2 第一次分析

此时应该有以下个地方值得注意

1) 该 sql 每天执行上千次,平均每次执行返回不到 10 行数据,但是平均逻辑读达到1.2W,可能存在性能问题。

2)ID 为 4,5 的执行计划路径中出现了两个全表扫描,看到这儿我们可以想到可能是没有合适的索引导致走了全表扫描从而执行效率低下。

3)ID 为 2 的执行计划路径出现了 FILTER,且 3,和 6 为其子路径,如果FILTER有两个及两个以上的子路径,那么他的执行原理将类似于嵌套循环,id 号最小的子路径如果返回行数较多,可能会导致多次执行id号更小的子路径,导致性能低下。一般存在 “OR EXISTS” 的时候会出现此情况,可以根据情况避免。

4)存在条件“ rownum<500 ”,但是从历史的执行情况来看,返回行数都远小于 500 行,此处我们先予以忽略。

3 第一次分析的处理

1)进过探查,发现存在两个表都有可用的索引,且两个表都只有几十 M 的大小

2)去掉“OR EXISTS”子句查看执行效率

此处可用看到,去掉 “OR EXISTS” 之后两个表走了合适的索引,并且执行效率极高。

SQL> SELECT 2 NVL(T.RELA_OFFER_SPEC_ID, SUBOS.SUB_OFFER_SPEC_ID) "offerSpecId" 3 FROM OFFER_SPEC_RELA T 4 LEFT JOIN OFFER_SPEC_GRP_RELA SUBOS 5 ON T.RELA_GRP_ID = SUBOS.OFFER_SPEC_GRP_ID 6 AND subos.start_dt <= SYSDATE 7 AND subos.end_dt >= SYSDATE 8 WHERE T.RELA_TYPE_CD = 2 9 AND t.start_dt <= SYSDATE 10 AND t.end_dt >= SYSDATE 11 AND T.OFFER_SPEC_ID = 109910000618; Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 510876366

Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("T"."RELA_TYPE_CD"=2 AND "T"."END_DT">=SYSDATE@! AND "T"."START_DT"<=SYSDATE@!) 3 - access("T"."OFFER_SPEC_ID"=109910000618) 4 - filter("SUBOS"."END_DT"(+)>=SYSDATE@! AND "SUBOS"."START_DT"(+)<=SYSDATE@!) 5 - access("T"."RELA_GRP_ID"="SUBOS"."OFFER_SPEC_GRP_ID"(+)) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 339 bytes sent via SQL*Net to client 510 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed

3)去掉 “OR EXISTS” 中的子句查看执行效率。

此处可用看到 “ OR EXISTS ” 中的子句单独执行返回行数并不多,且效率依旧很快。

SQL> SELECT A.OFFER_SPEC_GRP_ID 2 FROM OFFER_SPEC_GRP_RELA A 3 WHERE A.SUB_OFFER_SPEC_ID = 109910000618; OFFER_SPEC_GRP_ID ----------------- 100000048 109090086 Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 4223340843

Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"."SUB_OFFER_SPEC_ID"=109910000618) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 597 bytes sent via SQL*Net to client 521 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed

4)我们把该条 sql 语句分为 “OR EXISTS” 的子句和其他部分两块,到此我们可以看到,两块的执行效率都很高,但是合在一起就低了很多。在这种情况下,几乎可以确认,将该存在 “OR EXISTS” 的子句改写为 union 必将提升效率。

SQL> SELECT * 2 FROM 3 (SELECT NVL(T.RELA_OFFER_SPEC_ID, SUBOS.SUB_OFFER_SPEC_ID) "offerSpecId" 4 FROM OFFER_SPEC_RELA T 5 LEFT JOIN OFFER_SPEC_GRP_RELA SUBOS 6 ON T.RELA_GRP_ID = SUBOS.OFFER_SPEC_GRP_ID 7 AND subos.start_dt <= SYSDATE 8 AND subos.end_dt >= SYSDATE 9 WHERE T.RELA_TYPE_CD = 2 10 AND t.start_dt <= SYSDATE 11 AND t.end_dt >= SYSDATE 12 AND T.OFFER_SPEC_ID = 109910000618 13 UNION 14 SELECT NVL(T.RELA_OFFER_SPEC_ID, SUBOS.SUB_OFFER_SPEC_ID) "offerSpecId" 15 FROM OFFER_SPEC_RELA T 16 LEFT JOIN OFFER_SPEC_GRP_RELA SUBOS 17 ON T.RELA_GRP_ID = SUBOS.OFFER_SPEC_GRP_ID 18 AND subos.start_dt <= SYSDATE 19 AND subos.end_dt >= SYSDATE 20 WHERE T.RELA_TYPE_CD = 2 21 AND t.start_dt <= SYSDATE 22 AND t.end_dt >= SYSDATE 23 AND EXISTS 24 (SELECT A.OFFER_SPEC_GRP_ID 25 FROM OFFER_SPEC_GRP_RELA A 26 WHERE A.SUB_OFFER_SPEC_ID = 109910000618 27 AND T.OFFER_SPEC_GRP_ID = A.OFFER_SPEC_GRP_ID 28 ) 29 ) 30 WHERE rownum<500; Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 3072450155

Predicate Information (identified by operation id):

--------------------------------------------------- 1 - filter(ROWNUM<500) 3 - filter(ROWNUM<500) 6 - filter("T"."RELA_TYPE_CD"=2 AND "T"."END_DT">=SYSDATE@! AND "T"."START_DT"<=SYSDATE@!) 7 - access("T"."OFFER_SPEC_ID"=109910000618) 8 - filter("SUBOS"."END_DT"(+)>=SYSDATE@! AND "SUBOS"."START_DT"(+)<=SYSDATE@!) 9 - access("T"."RELA_GRP_ID"="SUBOS"."OFFER_SPEC_GRP_ID"(+)) 13 - access("A"."SUB_OFFER_SPEC_ID"=109910000618) 14 - filter("T"."RELA_TYPE_CD"=2 AND "T"."END_DT">=SYSDATE@! AND "T"."START_DT"<=SYSDATE@!) 15 - access("T"."OFFER_SPEC_GRP_ID"="A"."OFFER_SPEC_GRP_ID") filter("T"."OFFER_SPEC_GRP_ID" IS NOT NULL) 16 - filter("SUBOS"."END_DT"(+)>=SYSDATE@! AND "SUBOS"."START_DT"(+)<=SYSDATE@!) 17 - access("T"."RELA_GRP_ID"="SUBOS"."OFFER_SPEC_GRP_ID"(+)) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 11 consistent gets 0 physical reads 0 redo size 339 bytes sent via SQL*Net to client 510 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 0 rows processed

此处我们可以看到,改写之后逻辑读仅仅 11,较优化前提升了上千倍。到了此处,我们已经将 sql 优化到几乎最快的效率了。

4 第二次分析,确实改写能够提升效率,但是如果改写sql会涉及到修改代码,当前能否在不修改代码的情况下对其进行优化。

1)我们再来回顾一下最开始的执行计划路径。

我们可以看到 “ OR EXISTS ” 中的子句是在 ID 为 6 的路径才开始执行的,这儿有一个知识点即为一个 sql 中的子句,一般情况下默认会将其放到最后执行。

2)ID 为 4 , 5 的执行计划路径中在有高效索引的情况下却出现了两个全表扫描,可以推断 CBO 可能没有正常评估执行的 cost。

3)“OR EXISTS” 中的子句执行效率很快,返回行数并不多,我们可以考虑提升 CBO 将其提前执行,看能否影响 CBO 选择出更高效的执行计划。

SQL> SELECT NVL(T.RELA_OFFER_SPEC_ID, SUBOS.SUB_OFFER_SPEC_ID) "offerSpecId" 2 FROM OFFER_SPEC_RELA T 3 LEFT JOIN OFFER_SPEC_GRP_RELA SUBOS 4 ON T.RELA_GRP_ID = SUBOS.OFFER_SPEC_GRP_ID 5 AND subos.start_dt <= SYSDATE 6 AND subos.end_dt >= SYSDATE 7 WHERE T.RELA_TYPE_CD = 2 8 AND t.start_dt <= SYSDATE 9 AND t.end_dt >= SYSDATE 10 AND (T.OFFER_SPEC_ID = 109910000618 11 OR EXISTS 12 (SELECT /*+ push_subq */ A.OFFER_SPEC_GRP_ID 13 FROM OFFER_SPEC_GRP_RELA A 14 WHERE A.SUB_OFFER_SPEC_ID = 109910000618 15 AND T.OFFER_SPEC_GRP_ID = A.OFFER_SPEC_GRP_ID 16 )) 17 AND rownum<500; Elapsed: 00:00:00.03 Execution Plan ---------------------------------------------------------- Plan hash value: 849330408

--------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<500) 3 - filter("T"."RELA_TYPE_CD"=2 AND "T"."END_DT">=SYSDATE@! AND "T"."START_DT"<=SYSDATE@! AND ("T"."OFFER_SPEC_ID"=109910000618 OR EXISTS (SELECT /*+ PUSH_SUBQ */ 0 FROM "SPEC"."OFFER_SPEC_GRP_RELA" "A" WHERE "A"."OFFER_SPEC_GRP_ID"=:B1 AND "A"."SUB_OFFER_SPEC_ID"=109910000618))) 4 - access("A"."SUB_OFFER_SPEC_ID"=109910000618 AND "A"."OFFER_SPEC_GRP_ID"=:B1) 5 - filter("SUBOS"."END_DT"(+)>=SYSDATE@! AND "SUBOS"."START_DT"(+)<=SYSDATE@!) 6 - access("T"."RELA_GRP_ID"="SUBOS"."OFFER_SPEC_GRP_ID"(+)) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2531 consistent gets 0 physical reads 0 redo size 339 bytes sent via SQL*Net to client 510 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processedSYS@crmadb1> SELECT NVL(T.RELA_OFFER_SPEC_ID, SUBOS.SUB_OFFER_SPEC_ID) "offerSpecId" 2 FROM OFFER_SPEC_RELA T 3 LEFT JOIN OFFER_SPEC_GRP_RELA SUBOS 4 ON T.RELA_GRP_ID = SUBOS.OFFER_SPEC_GRP_ID 5 AND subos.start_dt <= SYSDATE 6 AND subos.end_dt >= SYSDATE 7 WHERE T.RELA_TYPE_CD = 2 8 AND t.start_dt <= SYSDATE 9 AND t.end_dt >= SYSDATE 10 AND (T.OFFER_SPEC_ID = 109910000618 11 OR EXISTS 12 (SELECT /*+ push_subq */ A.OFFER_SPEC_GRP_ID 13 FROM OFFER_SPEC_GRP_RELA A 14 WHERE A.SUB_OFFER_SPEC_ID = 109910000618 15 AND T.OFFER_SPEC_GRP_ID = A.OFFER_SPEC_GRP_ID 16 )) 17 AND rownum<500; Elapsed: 00:00:00.03 Execution Plan ---------------------------------------------------------- Plan hash value: 849330408 ---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<500) 3 - filter("T"."RELA_TYPE_CD"=2 AND "T"."END_DT">=SYSDATE@! AND "T"."START_DT"<=SYSDATE@! AND ("T"."OFFER_SPEC_ID"=109910000618 OR EXISTS (SELECT /*+ PUSH_SUBQ */ 0 FROM "SPEC"."OFFER_SPEC_GRP_RELA" "A" WHERE "A"."OFFER_SPEC_GRP_ID"=:B1 AND "A"."SUB_OFFER_SPEC_ID"=109910000618))) 4 - access("A"."SUB_OFFER_SPEC_ID"=109910000618 AND "A"."OFFER_SPEC_GRP_ID"=:B1) 5 - filter("SUBOS"."END_DT"(+)>=SYSDATE@! AND "SUBOS"."START_DT"(+)<=SYSDATE@!) 6 - access("T"."RELA_GRP_ID"="SUBOS"."OFFER_SPEC_GRP_ID"(+)) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2531 consistent gets 0 physical reads 0 redo size 339 bytes sent via SQL*Net to client 510 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed

此处我们在子句中加了一个 HINT /*+ push_subq */ ,该HINT的作用即使提醒 CBO 将子句提前执行。我们可以看到,执行效率较之前也得到了显著提升,逻辑读降低了 7 倍作用,虽然相对于改写效率还是高很多,但是在急需处理的情况下该方案还是更加可取的,此时对执行计划进行绑定即可,无需修改代码。

4)最后执行计划中还是存在全表扫描,我使用 hint 使其强制走索引查看情况:

SQL> SELECT /*+ index(@"SEL$9E43CB6E" "T"@"SEL$2") */ NVL(T.RELA_OFFER_SPEC_ID, SUBOS.SUB_OFFER_SPEC_ID) "offerSpecId" 2 FROM OFFER_SPEC_RELA T 3 LEFT JOIN OFFER_SPEC_GRP_RELA SUBOS 4 ON T.RELA_GRP_ID = SUBOS.OFFER_SPEC_GRP_ID 5 AND subos.start_dt <= SYSDATE 6 AND subos.end_dt >= SYSDATE 7 WHERE T.RELA_TYPE_CD = 2 8 AND t.start_dt <= SYSDATE 9 AND t.end_dt >= SYSDATE 10 AND (T.OFFER_SPEC_ID = 109910000618 11 OR EXISTS 12 (SELECT /*+ push_subq */ A.OFFER_SPEC_GRP_ID 13 FROM OFFER_SPEC_GRP_RELA A 14 WHERE A.SUB_OFFER_SPEC_ID = 109910000618 15 AND T.OFFER_SPEC_GRP_ID = A.OFFER_SPEC_GRP_ID 16 )) 17 AND rownum<500; Elapsed: 00:00:00.69 Execution Plan ---------------------------------------------------------- Plan hash value: 355757032 --------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------

1 - filter(ROWNUM<500) 3 - filter("T"."RELA_TYPE_CD"=2 AND "T"."END_DT">=SYSDATE@! AND "T"."START_DT"<=SYSDATE@! AND ("T"."OFFER_SPEC_ID"=109910000618 OR EXISTS (SELECT /*+ PUSH_SUBQ */ 0 FROM "SPEC"."OFFER_SPEC_GRP_RELA" "A" WHERE "A"."OFFER_SPEC_GRP_ID"=:B1 AND "A"."SUB_OFFER_SPEC_ID"=109910000618))) 5 - access("A"."SUB_OFFER_SPEC_ID"=109910000618 AND "A"."OFFER_SPEC_GRP_ID"=:B1) 6 - filter("SUBOS"."END_DT"(+)>=SYSDATE@! AND "SUBOS"."START_DT"(+)<=SYSDATE@!) 7 - access("T"."RELA_GRP_ID"="SUBOS"."OFFER_SPEC_GRP_ID"(+)) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 10527 consistent gets 406 physical reads 0 redo size 339 bytes sent via SQL*Net to client 510 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed

此时虽然走了索引,但是却是 ”INDEX FULL SCAN“ ,逻辑读也增加了很多,所以此时可以保持之前全表扫描的执行计划。

作者:黄堋

转载:意向文章下方留言。

本文分享自微信公众号 - 数据和云(OraNews)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2018-07-22

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

我来说两句

0 条评论
登录 后参与评论

相关文章

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

    黄廷忠(网名:认真就输) 云和恩墨技术专家 个人博客:http://www.htz.pw/ 本篇整理内容是黄廷忠在“云和恩墨大讲堂”微信分享中的讲解案例,S...

    数据和云
  • MySQL - 8种常见的SQL错误用法

    前言:MySQL在2016年仍然保持强劲的数据库流行度增长趋势。越来越多的客户将自己的应用建立在MySQL数据库之上,甚至是从Oracle迁移到MySQL上来。...

    数据和云
  • 深入剖析 Group Replication内核的引擎特性

    小编寄语 主库master与从库slave的切换不管是主动的还是被动的都需要外部干预才能进行,这与数据库内核本身是按照单机来设计的理念悉悉相关,并且数据库系统本...

    数据和云
  • 对于知识焦虑,这是我最诚恳的意见了。

    导语:收藏了一堆技术文章链接,关注了一堆公众号,却从未阅读? 一本又一本地买书,直到书架囤满,却从未拆封? 下载了得到、喜马拉雅、微信读书,却从未打开? 不要怀...

    腾讯大讲堂
  • MyBatis的“基于嵌套select”映射的剖析

    本文详细分析了MyBatis中“基于嵌套select”映射策略的性能缺陷、并给出了具体的实施建议,本文适合对MyBatis有一定使用经验的读者阅读,对MyBat...

    疯狂软件李刚
  • snowflake升级版全局id生成

    1. 背景 分布式系统或者微服务架构基本都采用了分库分表的设计,全局唯一id生成的需求变得很迫切。 传统的单体应用,使用单库,数据库中自增id可以很方便实现。分...

    aoho求索
  • MVCC原理探究及MySQL源码实现分析

    目录预览 数据库多版本读场景 MVCC实现原理 1、通过DB_ROLL_PT 回溯查找数据历史版本 2、通过read view判断行...

    沃趣科技
  • 【DB笔试面试733】在Oracle中,RAC中REMOTE_LISTENER的作用是什么?

    REMOTE_LISTENER参数主要用于RAC环境中监听器的远程注册,监听器的远程注册主要用于实现负载均衡。通常情况下,客户端发出的连接请求会首先被LOCAL...

    小麦苗DBA宝典
  • 【代码审计】MIPCMS 远程写入配置文件Getshell

    MIPCMS - 基于百度MIP移动加速器SEO优化后的网站系统。在审计代码中,发现一个可以远程写入配置文件Getshell的漏洞,感觉挺有意思的,分享...

    Bypass
  • 比特大陆被曝遭台积电断供芯片?官方回应:谣言

    4日,Coingeek网站称,他们从多家消息来源中获悉,比特大陆拖欠了台积电超过3亿美元的债务,台积电将不再向比特大陆提供芯片。

    新智元

扫码关注云+社区

领取腾讯云代金券