SQL优化案例——谓词推入导致SQL变慢

涉及知识：
1.SQL优化三大件
2.谓词推入的弊端

问题现象：在发版测试的过程中，发现有一条预算相关的语句十分缓慢。这里简单的把SQL贴一下。
SELECT purapplyrpttb.pk_org_v

FROM (select tb_pur.pk_pur_apply_h
....
FROM ((select hPurApplyTb.pk_pur_apply_h pk_pur_apply_h
...
FROM ec_pur_apply_b bPurApplyTb
left outer join ec_pur_apply_h hPurApplyTb
on bPurApplyTb.pk_pur_apply_h =
hPurApplyTb.pk_pur_apply_h
where 1 = 1
and (hPurApplyTb.pk_org in
(...)
left outer join (select projReqTb.pk_pur_project pk_pur_project
...
FROM ec_purproject_req projReqTb
...
left outer join (select SHA.pur_result_type co_type,
...
FROM ec_split_h SHA
inner join ec_split_supp_b SHB
on SHA.pk_split_h = SHB.pk_split_h
where SHA.dr = 0
and SHB.dr = 0) tb_split
on tb_split.pk_pur_project = tb_proj.pk_pur_project
...
WHERE purapplyrpttb.pk_org IN
...
ORDER BY purapplyrpttb.pk_pur_apply_h, purapplyrpttb.pk_pur_apply_b
问题分析：
这里首先说明一下，SQL优化需要观察的三大件。
1.select
2.from
3.where
这其实是废话。不过废话往往是真理。
一般SQL的慢，就慢在这三个部分。
1.select 通常缓慢的原因是出现了标量子查询，也就是字段中嵌套了select。通常我们通过left join进行优化。
2.from 通常这部分的缓慢是因为嵌套的表比较多。
3.where 也就是谓词。用来进行条件的筛选。这个案例中的谓词推入，就是将where条件中的语句推入到了上面的各个环节中。本来的执行计划应该先进行表的关联之后，再用谓词进行过滤。而oracle11g的新特性，会导致谓词的推入。使得谓词和每一个表都进行了过滤。而一旦发生这样的过滤，会发生未知的风险。有时候我们为了优化，在NC的数据库中直接禁用谓词推入。
这里我们看一下目前的执行计划：

Plan hash value: 1369001483

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |

| 0 | SELECT STATEMENT | | 1 | | 10063 |00:06:25.46 | 108M| 5253 | | | |
| 1 | SORT ORDER BY | | 1 | 40 | 10063 |00:06:25.46 | 108M| 5253 | 4518K| 894K| 4015K (0)|
|* 2 | HASH JOIN OUTER | | 1 | 40 | 10063 |00:06:25.44 | 108M| 5253 | 4808K| 1948K| 4440K (0)|
|* 3 | HASH JOIN OUTER | | 1 | 2 | 10063 |00:06:25.15 | 108M| 3684 | 4697K| 1953K| 4336K (0)|
|* 4 | HASH JOIN OUTER | | 1 | 1 | 10063 |00:06:24.88 | 108M| 602 | 4643K| 1956K| 4320K (0)|
| 5 | NESTED LOOPS OUTER | | 1 | 1 | 10063 |00:06:24.84 | 108M| 602 | | | |
| 6 | NESTED LOOPS | | 1 | 1 | 10063 |00:00:00.30 | 26456 | 602 | | | |
|* 7 | TABLE ACCESS FULL | EC_PUR_APPLY_B | 1 | 19457 | 20063 |00:00:00.19 | 1635 | 599 | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID | EC_PUR_APPLY_H | 20063 | 1 | 10063 |00:00:00.10 | 24821 | 3 | | | |
|* 9 | INDEX UNIQUE SCAN | PK_EC_PUR_APPLY_H | 20063 | 1 | 20063 |00:00:00.04 | 4758 | 0 | | | |
|* 10 | VIEW PUSHED PREDICATE | | 10063 | 1 | 10000 |00:06:24.52 | 108M| 0 | | | |
|* 11 | FILTER | | 10063 | | 100M|00:05:58.07 | 108M| 0 | | | |
| 12 | NESTED LOOPS | | 10063 | 5254 | 100M|00:05:35.50 | 108M| 0 | | | |
| 13 | NESTED LOOPS | | 10063 | 5254 | 100M|00:02:57.44 | 7587K| 0 | | | |
| 14 | NESTED LOOPS | | 10063 | 5254 | 100M|00:01:05.41 | 7587K| 0 | | | |
| 15 | TABLE ACCESS BY INDEX ROWID| BD_BILLTYPE | 10063 | 1 | 10063 |00:00:00.06 | 10072 | 0 | | | |
|* 16 | INDEX UNIQUE SCAN | PK_BD_BILLTYPE | 10063 | 1 | 10063 |00:00:00.03 | 9 | 0 | | | |
|* 17 | TABLE ACCESS FULL | EC_PURPROJECT_REQ | 10063 | 5254 | 100M|00:00:52.54 | 7577K| 0 | | | |
|* 18 | INDEX UNIQUE SCAN | PK_C_PUR_PROJECT_H | 100M| 1 | 100M|00:01:17.57 | 4 | 0 | | | |
|* 19 | TABLE ACCESS BY INDEX ROWID | EC_PUR_PROJECT_H | 100M| 1 | 100M|00:02:06.39 | 100M| 0 | | | |
| 20 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 7 | 0 | | | |
|* 21 | TABLE ACCESS FULL | EC_PUR_PROJECT_H | 1 | 1 | 1 |00:00:00.01 | 7 | 0 | | | |
| 22 | VIEW | | 1 | 1 | 0 |00:00:00.01 | 7 | 0 | | | |
| 23 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 7 | 0 | | | |
| 24 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 7 | 0 | | | |
|* 25 | TABLE ACCESS FULL | EC_SPLIT_SUPP_B | 1 | 1 | 0 |00:00:00.01 | 7 | 0 | | | |
|* 26 | INDEX UNIQUE SCAN | PK_EC_SPLIT_H | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 27 | TABLE ACCESS BY INDEX ROWID | EC_SPLIT_H | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 28 | VIEW | | 1 | 11 | 45 |00:00:00.26 | 3086 | 3082 | | | |
| 29 | HASH GROUP BY | | 1 | 11 | 45 |00:00:00.26 | 3086 | 3082 | 3279K| 1094K| 2474K (0)|
|* 30 | HASH JOIN | | 1 | 16495 | 16774 |00:00:00.25 | 3086 | 3082 | 1018K| 1018K| 1441K (0)|
| 31 | TABLE ACCESS FULL | CT_PU | 1 | 2996 | 3030 |00:00:00.08 | 440 | 438 | | | |
|* 32 | TABLE ACCESS FULL | CT_PU_B | 1 | 16495 | 16774 |00:00:00.16 | 2646 | 2644 | | | |
| 33 | VIEW | | 1 | 17 | 0 |00:00:00.27 | 12283 | 1569 | | | |
| 34 | HASH GROUP BY | | 1 | 17 | 0 |00:00:00.27 | 12283 | 1569 | 786K| 786K| |
|* 35 | HASH JOIN | | 1 | 60953 | 0 |00:00:00.27 | 12283 | 1569 | 2662K| 1253K| 3374K (0)|
| 36 | TABLE ACCESS FULL | PO_ORDER | 1 | 18763 | 18914 |00:00:00.16 | 1574 | 1569 | | | |
|* 37 | HASH JOIN | | 1 | 60953 | 0 |00:00:00.10 | 10709 | 0 | 1344K| 1344K| 739K (0)|
| 38 | INDEX FULL SCAN | PK_EC_SPLIT_H | 1 | 2 | 2 |00:00:00.01 | 1 | 0 | | | |
|* 39 | TABLE ACCESS FULL | PO_ORDER_B | 1 | 60953 | 61710 |00:00:00.08 | 10708 | 0 | | | |

Predicate Information (identified by operation id):

2 - access("TB_ORDER"."CECBILLID"="TB_SPLIT"."PK_SPLIT_H" AND "TB_ORDER"."CECBILLBID"="TB_SPLIT"."PK_SPLIT_SUPP_B")
3 - access("TB_CONTRACT"."CECBILLID"="TB_SPLIT"."PK_SPLIT_H" AND "TB_CONTRACT"."CECBILLBID"="TB_SPLIT"."PK_SPLIT_SUPP_B")
4 - access("TB_SPLIT"."PK_PUR_PROJECT"="TB_PROJ"."PK_PUR_PROJECT")
7 - filter("BPURAPPLYTB"."DR"=0)
8 - filter(("HPURAPPLYTB"."PK_ORG"='00018910000000001OD6' AND "HPURAPPLYTB"."DR"=0 AND NVL("HPURAPPLYTB"."ISFROMERP",'N')='N'))
9 - access("HPURAPPLYTB"."PK_PUR_APPLY_H"="BPURAPPLYTB"."PK_PUR_APPLY_H")
10 - filter(("TB_PROJ"."FIRSTHPK"=NVL("BPURAPPLYTB"."FIRSTHPK","BPURAPPLYTB"."PK_PUR_APPLY_H") AND
"TB_PROJ"."FIRSTBPK"=NVL("BPURAPPLYTB"."FIRSTBPK","BPURAPPLYTB"."PK_PUR_APPLY_B")))
11 - filter("PROJECTTB"."VERSION_NO"=)
16 - access("BD_BILLTYPE"."PK_BILLTYPEID"=DECODE("BPURAPPLYTB"."FIRSTBILLTYPE",'~','1001ZF100000000012EC','','1001ZF100000000012EC',"BPURAPPLYTB
"."FIRSTBILLTYPE","BPURAPPLYTB"."FIRSTBILLTYPE"))
17 - filter(("PROJREQTB"."DR"=0 AND "BD_BILLTYPE"."PK_BILLTYPECODE"="PROJREQTB"."FIRSTBILLTYPE"))
18 - access("PROJECTTB"."PK_PUR_PROJECT"="PROJREQTB"."PK_PUR_PROJECT")
19 - filter(("PROJECTTB"."DR"=0 AND NVL("PROJECTTB"."TEMP_SAVE",'N')='N'))
21 - filter("BILLNO"=:B1)
25 - filter("SHB"."DR"=0)
26 - access("SHA"."PK_SPLIT_H"="SHB"."PK_SPLIT_H")
27 - filter("SHA"."DR"=0)
30 - access("CTPB"."PK_CT_PU"="CTPH"."PK_CT_PU")
32 - filter("CTPB"."DR"=0)
35 - access("POHA"."PK_ORDER"="POBA"."PK_ORDER")
37 - access("POBA"."CECBILLID"="H"."PK_SPLIT_H")
39 - filter("POBA"."DR"=0)

Note

  • dynamic sampling used for this statement (level=2)

我们可以明显发现出现了|* 10 | VIEW PUSHED PREDICATE | | 10063 | 1 | 10000 |00:06:24.52 | 108M| 0 | | | |，也就是谓词推入。由于其中存在一些比较大的表，导致效率低下。如果直接走我们需要的执行计划，也就是先合并表后过滤的方式，就会快很多。

问题解决：

这里我们直接通过禁用谓词推入的hint来干扰执行计划。
left outer join (select /+ no_push_pred/ projReqTb.pk_pur_project pk_pur_project,
这里我们针对最大的表禁用谓词推入。
这里我们再查看执行计划可以发现：

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 2 | 1254 | 4893 (1)| 00:00:59 |
| 1 | SORT ORDER BY | | 2 | 1254 | 4893 (1)| 00:00:59 |
|* 2 | HASH JOIN OUTER | | 2 | 1254 | 4892 (1)| 00:00:59 |
|* 3 | HASH JOIN OUTER | | 1 | 561 | 1615 (1)| 00:00:20 |
|* 4 | HASH JOIN OUTER | | 1 | 495 | 802 (1)| 00:00:10 |
|* 5 | HASH JOIN OUTER | | 1 | 426 | 798 (1)| 00:00:10 |
| 6 | NESTED LOOPS | | 1 | 331 | 564 (0)| 00:00:07 |
| 7 | NESTED LOOPS | | 201 | 331 | 564 (0)| 00:00:07 |
|* 8 | TABLE ACCESS FULL | EC_PUR_APPLY_B | 201 | 46833 | 444 (1)| 00:00:06 |
|* 9 | INDEX UNIQUE SCAN | PK_EC_PUR_APPLY_H | 1 | | 1 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| EC_PUR_APPLY_H | 1 | 98 | 1 (0)| 00:00:01 |
| 11 | VIEW | | 1 | 95 | 233 (0)| 00:00:03 |
|* 12 | FILTER | | | | | |
|* 13 | HASH JOIN OUTER | | 1 | 212 | 230 (0)| 00:00:03 |
|* 14 | HASH JOIN | | 1 | 185 | 207 (0)| 00:00:03 |
|* 15 | TABLE ACCESS FULL | EC_PUR_PROJECT_H | 1 | 89 | 3 (0)| 00:00:01 |
|* 16 | TABLE ACCESS FULL | EC_PURPROJECT_REQ | 100 | 9600 | 204 (0)| 00:00:03 |
| 17 | TABLE ACCESS FULL | BD_BILLTYPE | 2416 | 65232 | 23 (0)| 00:00:01 |
| 18 | SORT AGGREGATE | | 1 | 40 | | |
|* 19 | TABLE ACCESS FULL | EC_PUR_PROJECT_H | 1 | 40 | 3 (0)| 00:00:01 |
| 20 | VIEW | | 1 | 69 | 4 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 117 | 4 (0)| 00:00:01 |
| 22 | NESTED LOOPS | | 1 | 117 | 4 (0)| 00:00:01 |
|* 23 | TABLE ACCESS FULL | EC_SPLIT_SUPP_B | 1 | 57 | 3 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | PK_EC_SPLIT_H | 1 | | 1 (0)| 00:00:01 |
|* 25 | TABLE ACCESS BY INDEX ROWID| EC_SPLIT_H | 1 | 60 | 1 (0)| 00:00:01 |
| 26 | VIEW | | 11 | 726 | 813 (1)| 00:00:10 |
| 27 | HASH GROUP BY | | 11 | 1331 | 813 (1)| 00:00:10 |
|* 28 | HASH JOIN | | 16495 | 1949K| 812 (1)| 00:00:10 |
| 29 | TABLE ACCESS FULL | CT_PU | 2996 | 184K| 111 (0)| 00:00:02 |
|* 30 | TABLE ACCESS FULL | CT_PU_B | 16495 | 934K| 700 (1)| 00:00:09 |
| 31 | VIEW | | 17 | 1122 | 3277 (1)| 00:00:40 |
| 32 | HASH GROUP BY | | 17 | 2465 | 3277 (1)| 00:00:40 |
|* 33 | HASH JOIN | | 60953 | 8631K| 3275 (1)| 00:00:40 |
| 34 | TABLE ACCESS FULL | PO_ORDER | 18763 | 1154K| 419 (1)| 00:00:06 |
|* 35 | HASH JOIN | | 60953 | 4881K| 2856 (1)| 00:00:35 |
| 36 | INDEX FULL SCAN | PK_EC_SPLIT_H | 2 | 44 | 1 (0)| 00:00:01 |
|* 37 | TABLE ACCESS FULL | PO_ORDER_B | 60953 | 3571K| 2854 (1)| 00:00:35 |

执行计划按照我们的思路走了。成功的将SQL执行时间，从6分钟减少到了1秒钟以内。

这里的优化，我们还可以使用物化视图或者是临时表的方式进行。都可以达到效果。
所以新特性还是需要谨慎使用。

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,530评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 86,403评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,120评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,770评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,758评论 5赞 367
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,649评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,021评论 3赞 398
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,675评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,931评论 1赞 299
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,659评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,751评论 1赞 330
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,410评论 4赞 321
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,004评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,969评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,203评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,042评论 2赞 350
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,493评论 2赞 343

推荐阅读更多精彩内容