oracle 谓词推入失效,SQL优化案例——谓词推入导致SQL变慢

最新推荐文章于 2023-09-30 03:58:16 发布

门捷列夫斯基

最新推荐文章于 2023-09-30 03:58:16 发布

阅读量480

点赞数

文章标签： oracle 谓词推入失效

涉及知识：

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

| 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,

这里我们针对最大的表禁用谓词推入。

这里我们再查看执行计划可以发现：

| 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秒钟以内。

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

所以新特性还是需要谨慎使用。

门捷列夫斯基

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
oracle 谓词推入失效,SQL优化案例——谓词推入导致SQL变慢

涉及知识：1.SQL优化三大件2.谓词推入的弊端问题现象：在发版测试的过程中，发现有一条预算相关的语句十分缓慢。这里简单的把SQL贴一下。SELECT purapplyrpttb.pk_org_vFROM (select tb_pur.pk_pur_apply_h....FROM ((select hPurApplyTb.pk_pur_apply_h pk_pur_apply_h...FROM e...
复制链接

扫一扫