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

叁歲伎倆 2022-06-02 00:40 216阅读 0赞

昨天开发反馈现场环境有个功能查询以前秒出，现在有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是没有办法的办法。