当前位置: 首页 > 调优 > 正文

SQL标量子查询改外连接的优化

下面是一条在一体机上面运行的SQL语句,消耗了大量的CPU,SQL为精简一些列后来测试的,但是对整个SQL的性能无影响。

欢迎大家加入QQ群:ORACLE数据库超级群 相互学习ORACLE技术

SELECT OFFER_SERV_SUM AS N37364,

       LOCAL_CODE LOCAL_CODE,

       AREA_ID AREA_ID,

       DVLP_AREA_ID MG_AREA_ID,

       DVLP_ORG_ID ORG_ID,

          CASE

             WHEN COLUMN_111111 IN

                     (SELECT LOCAL_ITEM_CODE

                        FROM XXXXXXXXXXX.XXXXXXXXX

                       WHERE PROV_TYPE_ID = 49 AND PROV_ITEM_ID = 64)

             THEN

                1

             ELSE

                0

          END

       || CASE

             WHEN TO_CHAR (COLUMN_111111) IN

                     (SELECT LOCAL_ITEM_CODE

                        FROM XXXXXXXXXXX.XXXXXXXXX A

                       WHERE     PROV_TYPE_ID = 49

                             AND PROV_ITEM_ID IN (85, 86, 68, 69, 70, 71))

             THEN

                1

             ELSE

                0

          END

       || CASE

             WHEN TO_CHAR (COLUMN_111111) IN

                     (SELECT COLUMN_111111

                        FROM XXXXXXXXXXX.BBBBBBBBBBBBB

                       WHERE     BT_CODE = 1

                             AND (   UPPER (NAME) LIKE ‘%4S%’

                                  OR NAME LIKE ‘%??%’))

             THEN

                1

             ELSE

                0

          END

       || CASE

             WHEN TO_CHAR (COLUMN_111111) IN

                     (SELECT LOCAL_ITEM_CODE

                        FROM XXXXXXXXXXX.XXXXXXXXX

                       WHERE PROV_TYPE_ID = 49 AND PROV_ITEM_ID = 64)

             THEN

                1

             ELSE

                0

          END

       || CASE

             WHEN TO_CHAR (COLUMN_111111) IN

                     (SELECT COLUMN_111111 FROM XXXXXXXXXXX.CCCCCCCCCCC)

             THEN

                1

             ELSE

                0

          END

       || CASE

             WHEN TO_CHAR (COLUMN_111111) IN

                     (SELECT COLUMN_111111

                        FROM XXXXXXXXXXX.BBBBBBBBBBBBB

                       WHERE     BT_CODE = 2

                             AND (   UPPER (NAME) LIKE ‘%4S%’

                                  OR NAME LIKE ‘%??%’))

             THEN

                1

             ELSE

                0

          END

          C_ALL

  FROM XXXXXXXXXXX.EEEEEEEEEEEEEEEEEEEE_D_201407 PARTITION (P20140727) A

 WHERE LOCAL_CODE = ‘XXX’

 

这条语句,运行了30分钟,都没有任何的发应,并且下面也可以看到处理的行数为190W

 

ERROR:

ORA-01013: user requested cancel of current operation

 

 

 

1908315 rows selected.

 

Elapsed: 00:27:09.83

 

Execution Plan

———————————————————-

 

 

Statistics

———————————————————-

         46  recursive calls

     325790  db block gets

  180241878  consistent gets

       3294  physical reads

      94580  redo size

   42593717  bytes sent via SQL*Net to client

    1399955  bytes received via SQL*Net from client

     127222  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

    1908315  rows processed

 

下面是SQL运行的统计信息,是平均值

             CPU              ELA             DISK              GET             ROWS             ROWS

        PRE EXEC         PRE EXEC         PRE EXEC         PRE EXEC         PRE EXEC      PRE FETCHES

—————- —————- —————- —————- —————- —————-

   5,083,968,119    5,350,213,227           22,610      338,280,770        4,302,704        4,302,704

下面是改写SQL后的运行结果

SELECT OFFER_SERV_SUM AS N37364,                                                                                                 

       LOCAL_CODE LOCAL_CODE,                                                                                                    

       AREA_ID AREA_ID,                                                                                                          

       DVLP_AREA_ID MG_AREA_ID,                                                                                                  

       DVLP_ORG_ID ORG_ID,                                                                                                       

          CASE WHEN a.COLUMN_111111 = b.LOCAL_ITEM_CODE THEN 1 ELSE 0 END                                                        

       || CASE                                                                                                                   

             WHEN TO_CHAR (a.COLUMN_111111) = e.LOCAL_ITEM_CODE THEN 1                                                           

             ELSE 0                                                                                                              

          END                                                                                                                    

       || CASE                                                                                                                   

             WHEN     TO_CHAR (a.COLUMN_111111) = c.COLUMN_111111                                                                

                  AND c.bt_code = 1                                                                                              

             THEN                                                                                                                

                1                                                                                                                

             ELSE                                                                                                                

                0                                                                                                                

          END                                                                                                                    

       || CASE WHEN a.COLUMN_111111 = b.LOCAL_ITEM_CODE THEN 1 ELSE 0 END                                                        

       || CASE                                                                                                                   

             WHEN TO_CHAR (a.COLUMN_111111) = d.COLUMN_111111 THEN 1                                                             

             ELSE 0                                                                                                              

          END                                                                                                                    

       || CASE                                                                                                                   

             WHEN     TO_CHAR (a.COLUMN_111111) = c.COLUMN_111111                                                                

                  AND c.bt_code = 2                                                                                              

             THEN                                                                                                                

                1                                                                                                                

             ELSE                                                                                                                

                0                                                                                                                

          END                                                                                                                    

          C_ALL                                                                                                                  

  FROM XXXXXXXXXXX.EEEEEEEEEEEEEEEEEEEE_D_201407 PARTITION (P20140727) A,                                                        

       (SELECT DISTINCT LOCAL_ITEM_CODE                                                                                          

          FROM XXXXXXXXXXX.XXXXXXXXX                                                                                             

         WHERE PROV_TYPE_ID = 49 AND PROV_ITEM_ID = 64) b,                                                                       

       (SELECT DISTINCT COLUMN_111111, bt_code                                                                                   

          FROM XXXXXXXXXXX.BBBBBBBBBBBBB                                                                                         

         WHERE     BT_CODE IN (1, 2)                                                                                             

               AND (UPPER (NAME) LIKE ‘%4S%’ OR NAME LIKE ‘%??%’)) c,                                                            

       (SELECT DISTINCT COLUMN_111111 FROM XXXXXXXXXXX.CCCCCCCCCCC) d,                                                           

       (SELECT DISTINCT LOCAL_ITEM_CODE                                                                                          

          FROM XXXXXXXXXXX.XXXXXXXXX A                                                                                           

         WHERE PROV_TYPE_ID = 49 AND PROV_ITEM_ID IN (85, 86, 68, 69, 70, 71)) e                                                 

 WHERE     LOCAL_CODE = ‘XXX’                                                                                                    

       AND a.COLUMN_111111 = b.LOCAL_ITEM_CODE(+)                                                                                

       AND TO_CHAR (a.COLUMN_111111) = c.COLUMN_111111(+)                                                                        

       AND TO_CHAR (a.COLUMN_111111) = d.COLUMN_111111(+)                                                                        

       AND TO_CHAR (a.COLUMN_111111) = e.LOCAL_ITEM_CODE(+)                                                                      

                                                                                                                                 

                                                                                                                                 

                                                                                                                                 

SQL> SELECT OFFER_SERV_SUM AS N37364,                                                                                            

  2         LOCAL_CODE LOCAL_CODE,                                                                                               

  3         AREA_ID AREA_ID,                                                                                                     

  4         DVLP_AREA_ID MG_AREA_ID,                                                                                             

  5         DVLP_ORG_ID ORG_ID,                                                                                                  

  6            CASE WHEN a.COLUMN_111111 = b.LOCAL_ITEM_CODE THEN 1 ELSE 0 END                                                   

  7         || CASE                                                                                                              

  8               WHEN TO_CHAR (a.COLUMN_111111) = e.LOCAL_ITEM_CODE THEN 1                                                      

  9               ELSE 0                                                                                                         

 10            END                                                                                                               

 11         || CASE                                                                                                              

 12               WHEN     TO_CHAR (a.COLUMN_111111) = c.COLUMN_111111                                                           

 13                    AND c.bt_code = 1                                                                                         

 14               THEN                                                                                                           

 15                  1                                                                                                           

 16               ELSE                                                                                                           

 17                  0                                                                                                           

 18            END                                                                                                               

 19         || CASE WHEN a.COLUMN_111111 = b.LOCAL_ITEM_CODE THEN 1 ELSE 0 END                                                   

 20         || CASE                                                                                                              

 21               WHEN TO_CHAR (a.COLUMN_111111) = d.COLUMN_111111 THEN 1                                                        

 22               ELSE 0                                                                                                         

 23            END                                                                                                               

 24         || CASE                                                                                                              

 25               WHEN     TO_CHAR (a.COLUMN_111111) = c.COLUMN_111111                                                           

 26                    AND c.bt_code = 2                                                                                         

 27               THEN                                                                                                           

 28                  1                                                                                                           

 29               ELSE                                                                                                           

 30                  0                                                                                                           

 31            END                                                                                                               

 32            C_ALL                                                                                                             

 33    FROM XXXXXXXXXXX.EEEEEEEEEEEEEEEEEEEE_D_201407 PARTITION (P20140727) A,                                                   

 34         (SELECT DISTINCT LOCAL_ITEM_CODE                                                                                     

 35            FROM XXXXXXXXXXX.XXXXXXXXX                                                                                        

 36           WHERE PROV_TYPE_ID = 49 AND PROV_ITEM_ID = 64) b,                                                                  

 37         (SELECT DISTINCT COLUMN_111111, bt_code                                                                              

 38            FROM XXXXXXXXXXX.BBBBBBBBBBBBB                                                                                    

 39           WHERE     BT_CODE IN (1, 2)                                                                                        

 40                 AND (UPPER (NAME) LIKE ‘%4S%’ OR NAME LIKE ‘%??%’)) c,                                                       

 41         (SELECT DISTINCT COLUMN_111111 FROM XXXXXXXXXXX.CCCCCCCCCCC) d,                                                      

 42         (SELECT DISTINCT LOCAL_ITEM_CODE                                                                                     

 43            FROM XXXXXXXXXXX.XXXXXXXXX A                                                                                      

 44           WHERE PROV_TYPE_ID = 49 AND PROV_ITEM_ID IN (85, 86, 68, 69, 70, 71)) e                                            

 45   WHERE     LOCAL_CODE = ‘XXX’                                                                                               

 46         AND a.COLUMN_111111 = b.LOCAL_ITEM_CODE(+)                                                                           

 47         AND TO_CHAR (a.COLUMN_111111) = c.COLUMN_111111(+)                                                                   

 48         AND TO_CHAR (a.COLUMN_111111) = d.COLUMN_111111(+)                                                                   

 49         AND TO_CHAR (a.COLUMN_111111) = e.LOCAL_ITEM_CODE(+);                                                                

                                                                                                                                 

4302704 rows selected.                                                                                                           

                                                                                                                                 

Elapsed: 00:00:28.65                                                                                                             

这里可以看到29S结果                                                                                                                               

Execution Plan                                                                                                                   

———————————————————-                                                                       

Plan hash value: 274455294                                                                                                       

                                                                                                                                 

———————————————————————————————————————————

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

———————————————————————————————————————————

|   0 | SELECT STATEMENT                |                               |  3962K|   498M|  3487   (7)| 00:00:01 |       |       |

|*  1 |  HASH JOIN RIGHT OUTER          |                               |  3962K|   498M|  3487   (7)| 00:00:01 |       |       |

|   2 |   VIEW                          |                               |   976 | 11712 |   175   (2)| 00:00:01 |       |       |

|   3 |    HASH UNIQUE                  |                               |   976 | 37088 |   175   (2)| 00:00:01 |       |       |

|*  4 |     TABLE ACCESS STORAGE FULL   | CODE_ITEM                     |   976 | 37088 |   174   (1)| 00:00:01 |       |       |

|*  5 |   HASH JOIN RIGHT OUTER         |                               |  3962K|   453M|  3300   (7)| 00:00:01 |       |       |

|   6 |    VIEW                         |                               |   932 | 12116 |     5  (20)| 00:00:01 |       |       |

|   7 |     HASH UNIQUE                 |                               |   932 | 12116 |     5  (20)| 00:00:01 |       |       |

|   8 |      TABLE ACCESS STORAGE FULL  | TY_SUIT_CFG                   |   932 | 12116 |     4   (0)| 00:00:01 |       |       |

|*  9 |    HASH JOIN RIGHT OUTER        |                               |  3962K|   404M|  3283   (7)| 00:00:01 |       |       |

|  10 |     VIEW                        |                               |   306 |  7956 |     6  (17)| 00:00:01 |       |       |

|  11 |      HASH UNIQUE                |                               |   306 | 39168 |     6  (17)| 00:00:01 |       |       |

|* 12 |       TABLE ACCESS STORAGE FULL | D_DRAGON_PLAN                 |   306 | 39168 |     5   (0)| 00:00:01 |       |       |

|* 13 |     HASH JOIN RIGHT OUTER       |                               |  3962K|   306M|  3266   (6)| 00:00:01 |       |       |

|  14 |      VIEW                       |                               |    60 |   720 |   175   (2)| 00:00:01 |       |       |

|  15 |       HASH UNIQUE               |                               |    60 |  2280 |   175   (2)| 00:00:01 |       |       |

|* 16 |        TABLE ACCESS STORAGE FULL| CODE_ITEM                     |    60 |  2280 |   174   (1)| 00:00:01 |       |       |

|  17 |      PARTITION RANGE SINGLE     |                               |  3962K|   260M|  3079   (6)| 00:00:01 |    27 |    27 |

|  18 |       PARTITION LIST SINGLE     |                               |  3962K|   260M|  3079   (6)| 00:00:01 |     1 |     1 |

|  19 |        TABLE ACCESS STORAGE FULL| DM_SP_SUB_OFFER_SERV_D_201407 |  3962K|   260M|  3079   (6)| 00:00:01 |   547 |   547 |

———————————————————————————————————————————

                                                                                                                                 

Predicate Information (identified by operation id):                                                                              

—————————————————                                                                              

                                                                                                                                 

   1 – access("A"."COLUMN_111111"=TO_NUMBER("B"."LOCAL_ITEM_CODE"(+)))                                                           

   4 – storage("PROV_TYPE_ID"=49 AND "PROV_ITEM_ID"=64)                                                                          

       filter("PROV_TYPE_ID"=49 AND "PROV_ITEM_ID"=64)                                                                           

   5 – access("D"."COLUMN_111111"(+)=TO_NUMBER(TO_CHAR("A"."COLUMN_111111")))                                                    

   9 – access("C"."COLUMN_111111"(+)=TO_NUMBER(TO_CHAR("A"."COLUMN_111111")))                                                    

  12 – storage(("BT_CODE"=1 OR "BT_CODE"=2) AND (UPPER("NAME") LIKE ‘%4S%’ OR "NAME" IS NOT NULL AND "NAME" IS NOT NULL          

              AND "NAME" LIKE ‘%??%’))                                                                                           

       filter(("BT_CODE"=1 OR "BT_CODE"=2) AND (UPPER("NAME") LIKE ‘%4S%’ OR "NAME" IS NOT NULL AND "NAME" IS NOT NULL           

              AND "NAME" LIKE ‘%??%’))                                                                                           

  13 – access("E"."LOCAL_ITEM_CODE"(+)=TO_CHAR("A"."COLUMN_111111"))                                                             

  16 – storage("PROV_TYPE_ID"=49 AND ("PROV_ITEM_ID"=68 OR "PROV_ITEM_ID"=69 OR "PROV_ITEM_ID"=70 OR "PROV_ITEM_ID"=71           

              OR "PROV_ITEM_ID"=85 OR "PROV_ITEM_ID"=86))                                                                        

       filter("PROV_TYPE_ID"=49 AND ("PROV_ITEM_ID"=68 OR "PROV_ITEM_ID"=69 OR "PROV_ITEM_ID"=70 OR "PROV_ITEM_ID"=71 OR         

              "PROV_ITEM_ID"=85 OR "PROV_ITEM_ID"=86))                                                                           

                                                                                                                                 

Note                                                                                                                             

—–                                                                                                                            

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

                                                                                                                                 

                                                                                                                                 

Statistics                                                                                                                       

———————————————————-                                                                       

          0  recursive calls                                                                                                     

          2  db block gets                                                                                                       

     303475  consistent gets                                                                                                     

          0  physical reads                                                                                                      

          0  redo size                                                                                                           

   96017715  bytes sent via SQL*Net to client                                                                                    

    3155830  bytes received via SQL*Net from client                                                                              

     286848  SQL*Net roundtrips to/from client                                                                                   

          0  sorts (memory)                                                                                                      

          0  sorts (disk)                                                                                                        

    4302704  rows processed                                                                                                      

优化到此结束

本文固定链接: http://www.htz.pw/2014/09/14/sql%e6%a0%87%e9%87%8f%e5%ad%90%e6%9f%a5%e8%af%a2%e6%94%b9%e5%a4%96%e8%bf%9e%e6%8e%a5%e7%9a%84%e4%bc%98%e5%8c%96.html | 认真就输

该日志由 huangtingzhong 于2014年09月14日发表在 调优 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: SQL标量子查询改外连接的优化 | 认真就输
关键字: