2017-12-27记一次快速SQL优化

最新推荐文章于 2024-02-21 13:15:36 发布

深圳gg

最新推荐文章于 2024-02-21 13:15:36 发布

阅读量382

点赞数

分类专栏： Oracle SQL优化

本文链接：https://blog.csdn.net/stevendbaguo/article/details/78918787

版权

Oracle SQL优化专栏收录该内容

130 篇文章 7 订阅

订阅专栏

昨天开发反馈现场环境有个功能查询以前秒出，现在有40多秒。

SELECT *

FROM (SELECT DISTINCT PRO.PROJECT_ID,
..........
FROM PMS_PROJECT PRO,
PMS_FBS_VERSION VER,
V_PMS_BPMS_TODOTASK V_BPMS
WHERE 1 = 1
AND VER.AUDIT_STATUS = '1'
AND (VER.VERSION_ID = '1' OR VER.FBS_CLASS = '2')
AND VER.PARENT_ID = '-1'
AND VER.PROJECT_ID = PRO.PROJECT_ID
AND PRO.PROJECT_STATUS < 40
AND VER.PROCESS_INSTANCE_ID = V_BPMS.MAIN_PROCESS_INS_ID(+)
AND PRO.PROJECT_TYPE_CODE = '3'
AND VER.PROJECT_TYPE_CODE = '3'
UNION
SELECT DISTINCT PMS_PROJECT.PROJECT_ID,
..........
FROM PMS_PROJECT PMS_PROJECT
WHERE PROJECT_ID NOT IN
(SELECT PROJECT_ID
FROM PMS_FBS_VERSION AA
WHERE AA.PARENT_ID = '-1'
AND AA.PROJECT_TYPE_CODE = '3')
AND EXISTS (SELECT 1
FROM PMS_FBS FBS
WHERE FBS.PROJECT_ID = PMS_PROJECT.PROJECT_ID
AND FBS.PROJECT_TYPE_CODE = '3'
AND FBS.PARENT_ID = '-1')
AND PMS_PROJECT.PROJECT_STATUS < 40
AND PMS_PROJECT.PROJECT_TYPE_CODE = '3') PRO
WHERE 1 = 1
AND PRO.PROJECT_TYPE_CODE = '3'
AND EXISTS (SELECT ''
FROM PMS_INVEST_PLAN PIP
WHERE PIP.PROJECT_ID = PRO.PROJECT_ID
AND PIP.INVEST_YEAR = '2017'
AND 1 = 1);
已选择1956行。
已用时间: 00: 00: 36.58
执行计划
----------------------------------------------------------
Plan hash value: 2472544721
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1072 | 162K (1)| 00:32:29 | | |
|* 1 | HASH JOIN RIGHT SEMI | | 1 | 1072 | 162K (1)| 00:32:29 | | |
| 2 | PARTITION LIST SINGLE | | 16602 | 405K| 84 (0)| 00:00:02 | KEY | KEY |
| 3 | TABLE ACCESS FULL | PMS_INVEST_PLAN | 16602 | 405K| 84 (0)| 00:00:02 | 7 | 7 |
| 4 | VIEW | | 1199 | 1225K| 162K (1)| 00:32:28 | | |
| 5 | SORT UNIQUE | | 1199 | 1162K| 162K (1)| 00:32:28 | | |
| 6 | UNION-ALL | | | | | | | |
| 7 | SORT AGGREGATE | | 1 | 36 | | | | |
|* 8 | TABLE ACCESS BY GLOBAL INDEX ROWID | PMS_FBS | 1 | 36 | 5 (0)| 00:00:01 | ROWID | ROWID |
|* 9 | INDEX RANGE SCAN | IDX_PMS_FBS_PROJECT_ID1 | 7 | | 3 (0)| 00:00:01 | | |
| 10 | SORT AGGREGATE | | 1 | 36 | | | | |
|* 11 | TABLE ACCESS BY GLOBAL INDEX ROWID| PMS_FBS | 1 | 36 | 5 (0)| 00:00:01 | ROWID | ROWID |
|* 12 | INDEX RANGE SCAN | IDX_PMS_FBS_PROJECT_ID1 | 7 | | 3 (0)| 00:00:01 | | |
|* 13 | HASH JOIN | | 620 | 333K| 1037 (1)| 00:00:13 | | |
|* 14 | HASH JOIN OUTER | | 866 | 126K| 621 (1)| 00:00:08 | | |
| 15 | PARTITION LIST SINGLE | | 866 | 64084 | 553 (1)| 00:00:07 | KEY | KEY |
|* 16 | TABLE ACCESS FULL | PMS_FBS_VERSION | 866 | 64084 | 553 (1)| 00:00:07 | 1 | 1 |
| 17 | TABLE ACCESS FULL | BPMS_RU_TODO_TASK_PMS | 2323 | 172K| 68 (0)| 00:00:01 | | |
| 18 | PARTITION LIST SINGLE | | 579 | 226K| 416 (1)| 00:00:05 | KEY | KEY |
|* 19 | TABLE ACCESS FULL | PMS_PROJECT | 579 | 226K| 416 (1)| 00:00:05 | 1 | 1 |
|* 20 | FILTER | | | | | | | |
|* 21 | HASH JOIN SEMI | | 579 | 247K| 970 (1)| 00:00:12 | | |
| 22 | PARTITION LIST SINGLE | | 579 | 226K| 416 (1)| 00:00:05 | KEY | KEY |
|* 23 | TABLE ACCESS FULL | PMS_PROJECT | 579 | 226K| 416 (1)| 00:00:05 | 1 | 1 |
| 24 | PARTITION LIST SINGLE | | 11175 | 403K| 554 (1)| 00:00:07 | KEY | KEY |
|* 25 | TABLE ACCESS FULL | PMS_FBS | 11175 | 403K| 554 (1)| 00:00:07 | 1 | 1 |
| 26 | PARTITION LIST SINGLE | | 1 | 34 | 553 (1)| 00:00:07 | KEY | KEY |
|* 27 | TABLE ACCESS FULL | PMS_FBS_VERSION | 1 | 34 | 553 (1)| 00:00:07 | 1 | 1 |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("PIP"."PROJECT_ID"="PRO"."PROJECT_ID")
8 - filter("F"."PARENT_ID"='-1')
9 - access("F"."PROJECT_ID"=:B1)
11 - filter("F"."PARENT_ID"='-1')
12 - access("F"."PROJECT_ID"=:B1)
13 - access("VER"."PROJECT_ID"="PRO"."PROJECT_ID")
14 - access("VER"."PROCESS_INSTANCE_ID"="BRTT"."MAIN_PROCESS_INS_ID"(+))
16 - filter("VER"."PARENT_ID"='-1' AND "VER"."AUDIT_STATUS"=1 AND ("VER"."VERSION_ID"='1' OR "VER"."FBS_CLASS"=2))
19 - filter(TO_NUMBER("PRO"."PROJECT_STATUS")<40)
20 - filter( NOT EXISTS (SELECT 0 FROM "PMS_FBS_VERSION" "AA" WHERE "AA"."PARENT_ID"='-1' AND LNNVL("PROJECT_ID"<>:B1)))
21 - access("FBS"."PROJECT_ID"="PMS_PROJECT"."PROJECT_ID")
23 - filter(TO_NUMBER("PMS_PROJECT"."PROJECT_STATUS")<40)
25 - filter("FBS"."PARENT_ID"='-1')
27 - filter("AA"."PARENT_ID"='-1' AND LNNVL("PROJECT_ID"<>:B1))
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
13934138 consistent gets
0 physical reads
0 redo size
346145 bytes sent via SQL*Net to client
1790 bytes received via SQL*Net from client
132 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1956 rows processed

获取现场的auto结果之后，10秒钟我就定位到问题了。逻辑读非常高，说明有大量的循环，filter算法类似nestloop，简单的说，第20行如果返回1000条数据，那PMS_FBS_VERSION的分区扫描就是1000次，而PMS_FBS_VERSION这张表的数据有几十万，所以问题就在这里。

解决方案是什么呢？

现在主要通过一些手段来改变执行计划，我的原则是尽量不要使用hint。

1.收集表的直方图，因为我发现PMS_FBS_VERSION中的PARENT_ID重复率非常高，我希望通过收集统计信息来改变执行计划。

exec dbms_stats.gather_table_stats(user,'PMS_FBS_VERSION',cascade => true,method_opt => 'for columns size auto PARENT_ID ',no_invalidate=>FALSE);

收集之后，发现执行计划没有变。继续收集其他几张表的统计信息，连带着收集直方图，执行计划依然没有变化。

2.23 - filter(TO_NUMBER("PMS_PROJECT"."PROJECT_STATUS")<40)，发现有隐式转换的问题，以前碰到过由于隐式转换导致走错执行计划的，然后消除了隐式转换，发现执行计划还是未变。

3.难道要用hint吗？当然不用，还有一招，就是not in 转换成左连接+ is not null，执行计划变了，秒出。

SELECT *
FROM (SELECT DISTINCT PRO.PROJECT_ID,
..........
FROM PMS_PROJECT PRO,
PMS_FBS_VERSION VER,
V_PMS_BPMS_TODOTASK V_BPMS
WHERE 1 = 1
AND VER.AUDIT_STATUS = '1'
AND (VER.VERSION_ID = '1' OR VER.FBS_CLASS = '2')
AND VER.PARENT_ID = '-1'
AND VER.PROJECT_ID = PRO.PROJECT_ID
AND TO_number(PRO.PROJECT_STATUS) < 40
AND VER.PROCESS_INSTANCE_ID = V_BPMS.MAIN_PROCESS_INS_ID(+)
AND PRO.PROJECT_TYPE_CODE = '3'
AND VER.PROJECT_TYPE_CODE = '3'
UNION
SELECT DISTINCT PMS_PROJECT.PROJECT_ID,
..........
FROM PMS_PROJECT PMS_PROJECT,(SELECT PROJECT_ID
FROM PMS_FBS_VERSION AA
WHERE AA.PARENT_ID = '-1'
AND AA.PROJECT_TYPE_CODE = '3') bb
WHERE PMS_PROJECT.PROJECT_ID = bb.PROJECT_ID(+)
and bb.project_id is null
AND EXISTS (SELECT 1
FROM PMS_FBS FBS
WHERE FBS.PROJECT_ID = PMS_PROJECT.PROJECT_ID
AND FBS.PROJECT_TYPE_CODE = '3'
AND FBS.PARENT_ID = '-1')
AND to_number(PMS_PROJECT.PROJECT_STATUS) < 40
AND PMS_PROJECT.PROJECT_TYPE_CODE = '3') PRO
WHERE 1 = 1
AND PRO.PROJECT_TYPE_CODE = '3'
AND EXISTS (SELECT ''
FROM PMS_INVEST_PLAN PIP
WHERE PIP.PROJECT_ID = PRO.PROJECT_ID
AND PIP.INVEST_YEAR = '2017'
AND 1 = 1)
已选择1956行。
已用时间: 00: 00: 00.42
执行计划
----------------------------------------------------------
Plan hash value: 3604279309
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1072 | 2116 (1)| 00:00:26 | | |
|* 1 | HASH JOIN RIGHT SEMI | | 1 | 1072 | 2116 (1)| 00:00:26 | | |
| 2 | PARTITION LIST SINGLE | | 16602 | 405K| 84 (0)| 00:00:02 | KEY | KEY |
| 3 | TABLE ACCESS FULL | PMS_INVEST_PLAN | 16602 | 405K| 84 (0)| 00:00:02 | 7 | 7 |
| 4 | VIEW | | 627 | 641K| 2032 (1)| 00:00:25 | | |
| 5 | SORT UNIQUE | | 627 | 336K| 2032 (1)| 00:00:25 | | |
| 6 | UNION-ALL | | | | | | | |
| 7 | SORT AGGREGATE | | 1 | 36 | | | | |
|* 8 | TABLE ACCESS BY GLOBAL INDEX ROWID | PMS_FBS | 1 | 36 | 5 (0)| 00:00:01 | ROWID | ROWID |
|* 9 | INDEX RANGE SCAN | IDX_PMS_FBS_PROJECT_ID1 | 7 | | 3 (0)| 00:00:01 | | |
| 10 | SORT AGGREGATE | | 1 | 36 | | | | |
|* 11 | TABLE ACCESS BY GLOBAL INDEX ROWID| PMS_FBS | 1 | 36 | 5 (0)| 00:00:01 | ROWID | ROWID |
|* 12 | INDEX RANGE SCAN | IDX_PMS_FBS_PROJECT_ID1 | 7 | | 3 (0)| 00:00:01 | | |
|* 13 | HASH JOIN | | 621 | 334K| 1037 (1)| 00:00:13 | | |
|* 14 | HASH JOIN OUTER | | 883 | 129K| 621 (1)| 00:00:08 | | |
| 15 | PARTITION LIST SINGLE | | 883 | 65342 | 553 (1)| 00:00:07 | KEY | KEY |
|* 16 | TABLE ACCESS FULL | PMS_FBS_VERSION | 883 | 65342 | 553 (1)| 00:00:07 | 1 | 1 |
| 17 | TABLE ACCESS FULL | BPMS_RU_TODO_TASK_PMS | 2323 | 172K| 68 (0)| 00:00:01 | | |
| 18 | PARTITION LIST SINGLE | | 579 | 226K| 416 (1)| 00:00:05 | KEY | KEY |
|* 19 | TABLE ACCESS FULL | PMS_PROJECT | 579 | 226K| 416 (1)| 00:00:05 | 1 | 1 |
| 20 | NESTED LOOPS SEMI | | 6 | 2832 | 993 (1)| 00:00:12 | | |
|* 21 | HASH JOIN ANTI | | 6 | 2610 | 969 (1)| 00:00:12 | | |
| 22 | PARTITION LIST SINGLE | | 579 | 226K| 416 (1)| 00:00:05 | KEY | KEY |
|* 23 | TABLE ACCESS FULL | PMS_PROJECT | 579 | 226K| 416 (1)| 00:00:05 | 1 | 1 |
| 24 | PARTITION LIST SINGLE | | 5630 | 186K| 553 (1)| 00:00:07 | KEY | KEY |
|* 25 | TABLE ACCESS FULL | PMS_FBS_VERSION | 5630 | 186K| 553 (1)| 00:00:07 | 1 | 1 |
|* 26 | TABLE ACCESS BY GLOBAL INDEX ROWID | PMS_FBS | 11495 | 415K| 4 (0)| 00:00:01 | 1 | 1 |
|* 27 | INDEX RANGE SCAN | IDX_PMS_FBS_PROJECT_ID1 | 7 | | 2 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("PIP"."PROJECT_ID"="PRO"."PROJECT_ID")
8 - filter("F"."PARENT_ID"='-1')
9 - access("F"."PROJECT_ID"=:B1)
11 - filter("F"."PARENT_ID"='-1')
12 - access("F"."PROJECT_ID"=:B1)
13 - access("VER"."PROJECT_ID"="PRO"."PROJECT_ID")
14 - access("VER"."PROCESS_INSTANCE_ID"="BRTT"."MAIN_PROCESS_INS_ID"(+))
16 - filter("VER"."PARENT_ID"='-1' AND "VER"."AUDIT_STATUS"=1 AND ("VER"."VERSION_ID"='1' OR "VER"."FBS_CLASS"=2))
19 - filter(TO_NUMBER("PRO"."PROJECT_STATUS")<40)
21 - access("PMS_PROJECT"."PROJECT_ID"="PROJECT_ID")
23 - filter(TO_NUMBER("PMS_PROJECT"."PROJECT_STATUS")<40)
25 - filter("AA"."PARENT_ID"='-1')
26 - filter("FBS"."PARENT_ID"='-1' AND "FBS"."PROJECT_TYPE_CODE"='3')
27 - access("FBS"."PROJECT_ID"="PMS_PROJECT"."PROJECT_ID")
统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
29261 consistent gets
0 physical reads
0 redo size
346145 bytes sent via SQL*Net to client
1790 bytes received via SQL*Net from client
132 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)

1956 rows processed

总结：要想快速解决一个问题，首先要能快速定位到问题，然后有的放矢，执行计划的基础算法很重要，否则你都不知道数据库干了什么。可以想想CBO为什么会走错，不能改变它的想法的情况下，通过SQL改写是一种不错的做法，使用hint是没有办法的办法。