ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 一条SQL的优化,3个小时--->6秒到底什么在作怪?(tom_fans )

一条SQL的优化,3个小时--->6秒到底什么在作怪?(tom_fans )

原创 Linux操作系统 作者:Aminiy 时间:2011-04-10 15:47:28 0 删除 编辑
[quote]原帖由 tom_fans 于 2010-8-12 20:21 发表
我这里有个系统,每天会跑一个JOB,简单的说只有一条select,这个SELECT不知道多久就开始跑3个小时。至于为什么以前没优化,或者如何就不提了。

这条SQL是因为NOT IN慢。也许有太多not in 的优化方式,但是这次我要创新。我只是想表示一个观点,SQL没有规则可循,根本就不需要规则。SQL优化器的优化思路就是人本身的优化的思路。我从来不记优化器什么能做,什么不能做。也不会去刻意记住什么情况会用索引,什么情况不用。 我只记住nest loop. hash join, merge join的原理。

数据版本:9.2.0.4,跑CBO。

SQL原型:
SELECT '306400',      
       TA_MDL_SELL_IN_P.MDL_CD,      
       TO_CHAR(TA_MDL_SELL_IN_P.WEEK - 1, 'YYYYMMDD'),      
       SUM(NVL(TA_MDL_SELL_IN_P.PRD_REQ_QTY, 0)),      
       '1',      
       '0501'
  FROM TA_MDL_SELL_IN_P, TX_COMN_MDL_MST, TX_HRCH_MST
WHERE TA_MDL_SELL_IN_P.MDL_CD NOT IN
       (SELECT 'P' || A.MOD MDL_CD      
          FROM (SELECT SUBSTR(T.MDL_CD, 2, LENGTH(T.MDL_CD) - 1) MOD               
                  FROM TA_MDL_SELL_IN_P T               
                 WHERE T.MDL_CD LIKE 'P%'               
                INTERSECT               
                SELECT SUBSTR(P.MDL_CD, 2, LENGTH(P.MDL_CD) - 1) MOD               
                  FROM TA_MDL_SELL_IN_P P               
                 WHERE P.MDL_CD LIKE 'N%') A        
        UNION        
        SELECT DISTINCT TA_MDL_DEF_FLG.MDL_CD        
          FROM TA_MDL_DEF_FLG        
         WHERE TA_MDL_DEF_FLG.DISCON_FLG = '1')      
   AND TA_MDL_SELL_IN_P.PL_CD = TX_COMN_MDL_MST.PL_CD      
   AND TA_MDL_SELL_IN_P.KIND_CD = TX_COMN_MDL_MST.KIND_CD     
   AND TA_MDL_SELL_IN_P.MDL_CD = TX_COMN_MDL_MST.MDL_CD     
   AND TX_COMN_MDL_MST.FCST_MDL_CD IN (1, 2, 3, 4)      
   AND TX_COMN_MDL_MST.DLT_FLG = 0      
   AND TA_MDL_SELL_IN_P.WEEK > TO_DATE('一个变量', 'YYYYMMDD')      
   AND TA_MDL_SELL_IN_P.CHNL_CD = '1'      
   AND TX_COMN_MDL_MST.PL_CD = TX_HRCH_MST.PL_CD     
   AND TX_COMN_MDL_MST.KIND_CD = TX_HRCH_MST.KIND_CD     
   AND TX_COMN_MDL_MST.KIND_DTL_CD = TX_HRCH_MST.KIND_DTL_CD     
   AND TRIM(TX_HRCH_MST.CAT_CD) = trim(‘一个固定的变量‘)
GROUP BY TA_MDL_SELL_IN_P.MDL_CD, TA_MDL_SELL_IN_P.WEEK
ORDER BY TA_MDL_SELL_IN_P.MDL_CD, TA_MDL_SELL_IN_P.WEEK

这是一条标准的开发喜欢写的SQL类型,多看几眼就知道,其实这条SQL非常简单,只不过条件多了一些,表也不过4张。那么以前为什么跑三个小时?其实就是因为not in.

SQL> select count(*) from TA_MDL_SELL_IN_P;
  COUNT(*)
----------
    610194

SQL> select count(*) from  TX_COMN_MDL_MST
  2  ;
  COUNT(*)
----------
      9780


SQL> select count(*) from  TX_HRCH_MST;
  COUNT(*)
----------
       435


SQL> select count(*) from  TA_MDL_DEF_FLG;
  COUNT(*)
----------
      4513

最后得到数据是1W多条。

当开发把这条SQL给我的时候,给我的感觉就是SQL太简单,表也不大,计划也是标准的,所谓标准就是你看到这个SQL就知道大概怎么跑。仅仅是一个NOT IN不舒服,因为毕竟TA_MDL_SELL_IN_P有60W,过滤掉条件也有40W左右。40W去NOT IN 一个子查询(子查询大概每次要3秒),很显然会很慢。

先看看计划:
-------------------------------------------------------------------------------------------
| Id  | Operation                         |  Name                 | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                       |     1 |   104 |    23 |
|   1 |  SORT GROUP BY                    |                       |     1 |   104 |    23 |
|   2 |   TABLE ACCESS BY INDEX ROWID     | TA_MDL_SELL_IN_P      |     1 |    37 |     3 |
|   3 |    NESTED LOOPS                   |                       |     1 |   104 |    21 |
|   4 |     HASH JOIN                     |                       |     1 |    67 |    18 |
|   5 |      TABLE ACCESS FULL            | TX_HRCH_MST           |    11 |   352 |     3 |
|   6 |      TABLE ACCESS FULL            | TX_COMN_MDL_MST       |   730 | 25550 |    14 |
|   7 |     INDEX RANGE SCAN              | PK_TA_MDL_SELL_IN_P   |     1 |       |     2 |
|   8 |      SORT UNIQUE                  |                       |  1530 | 24480 |  1067 |
|   9 |       UNION-ALL                   |                       |       |       |       |
|  10 |        VIEW                       |                       |  1529 | 24464 |  1056 |
|  11 |         INTERSECTION              |                       |       |       |       |
|  12 |          SORT UNIQUE              |                       |  1635 | 24525 |       |
|  13 |           INDEX FAST FULL SCAN    | TA_MDL_SELL_IN_P_I01  |  1635 | 24525 |   520 |
|  14 |          SORT UNIQUE              |                       |  1529 | 22935 |       |
|  15 |           INDEX FAST FULL SCAN    | TA_MDL_SELL_IN_P_I01  |  1529 | 22935 |   520 |
|  16 |        TABLE ACCESS BY INDEX ROWID| TA_MDL_DEF_FLG        |     1 |    16 |     2 |
|  17 |         INDEX RANGE SCAN          | PK_TA_MDL_DEF_FLG     |     1 |       |     1 |
-------------------------------------------------------------------------------------------

我的思路是:

既然是NOT IN,那就是要TA_MDL_SELL_IN_P.MDL_CD不在子查询里的数据才要。如果这个SQL是我写,我肯定也会写NOT IN,最简单。
为什么不能把思路掉回来呢? 既然你要查询不在的,我就先查询TA_MDL_SELL_IN_P.MDL_CD包含在子查询的,那很显然就会变成等值查询,走HASH肯定不会慢。

找到了包含的数据,再通过TA_MDL_SELL_IN_P和这个临时数据做MINUS取出差值。这和直接用NOT IN是同一个道理。

修改后的SQL:

SELECT '306400',      
       E.MDL_CD,      
       TO_CHAR(E.WEEK - 1, 'YYYYMMDD'),      
       SUM(NVL(E.PRD_REQ_QTY, 0)),      
       '1',      
       '0501'
  from (select A.*
          from (select TA_MDL_SELL_IN_P.MDL_CD      MDL_CD,
                        TA_MDL_SELL_IN_P.week        week,
                        TA_MDL_SELL_IN_P.PRD_REQ_QTY PRD_REQ_QTY
                   FROM TA_MDL_SELL_IN_P, TX_COMN_MDL_MST, TX_HRCH_MST                 
                  WHERE                 
                  TA_MDL_SELL_IN_P.PL_CD = TX_COMN_MDL_MST.PL_CD                 
               AND TA_MDL_SELL_IN_P.KIND_CD = TX_COMN_MDL_MST.KIND_CD               
               AND TA_MDL_SELL_IN_P.MDL_CD = TX_COMN_MDL_MST.MDL_CD                 
               AND TX_COMN_MDL_MST.FCST_MDL_CD IN (1, 2, 3, 4)                 
               AND TX_COMN_MDL_MST.DLT_FLG = 0                 
               AND TA_MDL_SELL_IN_P.WEEK > TO_DATE('20100809', 'YYYYMMDD')               
               AND TA_MDL_SELL_IN_P.CHNL_CD = '1'               
               AND TX_COMN_MDL_MST.PL_CD = TX_HRCH_MST.PL_CD               
               AND TX_COMN_MDL_MST.KIND_CD = TX_HRCH_MST.KIND_CD                 
               AND TX_COMN_MDL_MST.KIND_DTL_CD = TX_HRCH_MST.KIND_DTL_CD                 
               AND TRIM(TX_HRCH_MST.CAT_CD) = trim('VAIO')) A,               
               (select C.MDL_CD MDL_CD
                  from TA_MDL_SELL_IN_P C
                minus (select distinct A.MDL_CD
                        from TA_MDL_SELL_IN_P A,
                             (SELECT 'P' || A.MOD MDL_CD
                                FROM (SELECT SUBSTR(T.MDL_CD,
                                                    2,
                                                    LENGTH(T.MDL_CD) - 1) MOD
                                        FROM TA_MDL_SELL_IN_P T
                                       WHERE T.MDL_CD LIKE 'P%'
                                      INTERSECT
                                      SELECT SUBSTR(P.MDL_CD,
                                                    2,
                                                    LENGTH(P.MDL_CD) - 1) MOD
                                        FROM TA_MDL_SELL_IN_P P
                                       WHERE P.MDL_CD LIKE 'N%') A
                              UNION
                              SELECT DISTINCT TA_MDL_DEF_FLG.MDL_CD
                                FROM TA_MDL_DEF_FLG
                               WHERE TA_MDL_DEF_FLG.DISCON_FLG = '1') B
                       where A.MDL_CD = B.MDL_CD)) D
         where A.mdl_cd = D.mdl_cd) E
GROUP BY E.MDL_CD, E.WEEK
ORDER BY E.MDL_CD, E.WEEK

变换后就等于是NOT IN换成2个等值连接+MINUS

计划:
--------------------------------------------------------------------------------------------------
| Id  | Operation                        |  Name                 | Rows  | Bytes |TempSpc| Cost  |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                       |     1 |   121 |       | 15951 |
|   1 |  SORT GROUP BY                   |                       |     1 |   121 |       | 15951 |
|   2 |   HASH JOIN                      |                       |     7 |   847 |       | 15949 |
|   3 |    TABLE ACCESS BY INDEX ROWID   | TA_MDL_SELL_IN_P      |     3 |   111 |       |     3 |
|   4 |     NESTED LOOPS                 |                       |     1 |   104 |       |    21 |
|   5 |      HASH JOIN                   |                       |     1 |    67 |       |    18 |
|   6 |       TABLE ACCESS FULL          | TX_HRCH_MST           |    11 |   352 |       |     3 |
|   7 |       TABLE ACCESS FULL          | TX_COMN_MDL_MST       |   730 | 25550 |       |    14 |
|   8 |      INDEX RANGE SCAN            | PK_TA_MDL_SELL_IN_P   |     1 |       |       |     2 |
|   9 |    VIEW                          |                       |   392K|  6511K|       | 15924 |
|  10 |     MINUS                        |                       |       |       |       |       |
|  11 |      SORT UNIQUE                 |                       |   392K|  5745K|    18M|       |
|  12 |       INDEX FAST FULL SCAN       | TA_MDL_SELL_IN_P_I01  |   392K|  5745K|       |   520 |
|  13 |      SORT UNIQUE                 |                       |     1 |    31 |       |       |
|  14 |       HASH JOIN                  |                       |  2085K|    61M|   848K|  2245 |
|  15 |        VIEW                      |                       | 30744 |   480K|       |  1445 |
|  16 |         SORT UNIQUE              |                       | 30744 |   480K|  1448K|  1445 |
|  17 |          UNION-ALL               |                       |       |       |       |       |
|  18 |           VIEW                   |                       | 30582 |   477K|       |  1305 |
|  19 |            INTERSECTION          |                       |       |       |       |       |
|  20 |             SORT UNIQUE          |                       | 32698 |   478K|  1560K|       |
|  21 |              INDEX FAST FULL SCAN| TA_MDL_SELL_IN_P_I01  | 32698 |   478K|       |   520 |
|  22 |             SORT UNIQUE          |                       | 30582 |   447K|  1448K|       |
|  23 |              INDEX FAST FULL SCAN| TA_MDL_SELL_IN_P_I01  | 30582 |   447K|       |   520 |
|  24 |           TABLE ACCESS FULL      | TA_MDL_DEF_FLG        |   162 |  2592 |       |     7 |
|  25 |        INDEX FAST FULL SCAN      | TA_MDL_SELL_IN_P_I01  |   392K|  5745K|       |   520 |
--------------------------------------------------------------------------------------------------

SQL> set timing on
SQL> select count(*) from (SELECT '306400',      
  2         E.MDL_CD,      
  3         TO_CHAR(E.WEEK - 1, 'YYYYMMDD'),      
  4         SUM(NVL(E.PRD_REQ_QTY, 0)),      
  5         '1',      
  6         '0501'
  7    from (select A.*
  8            from (select TA_MDL_SELL_IN_P.MDL_CD      MDL_CD,
  9                          TA_MDL_SELL_IN_P.week        week,
10                          TA_MDL_SELL_IN_P.PRD_REQ_QTY PRD_REQ_QTY
11                     FROM TA_MDL_SELL_IN_P, TX_COMN_MDL_MST, TX_HRCH_MST                 
12                    WHERE                 
13                    TA_MDL_SELL_IN_P.PL_CD = TX_COMN_MDL_MST.PL_CD                 
14                 AND TA_MDL_SELL_IN_P.KIND_CD = TX_COMN_MDL_MST.KIND_CD               
15                 AND TA_MDL_SELL_IN_P.MDL_CD = TX_COMN_MDL_MST.MDL_CD                 
16                 AND TX_COMN_MDL_MST.FCST_MDL_CD IN (1, 2, 3, 4)                 
17                 AND TX_COMN_MDL_MST.DLT_FLG = 0                 
18                 AND TA_MDL_SELL_IN_P.WEEK > TO_DATE('20100809', 'YYYYMMDD')               
19                 AND TA_MDL_SELL_IN_P.CHNL_CD = '1'               
20                 AND TX_COMN_MDL_MST.PL_CD = TX_HRCH_MST.PL_CD               
21                 AND TX_COMN_MDL_MST.KIND_CD = TX_HRCH_MST.KIND_CD                 
               AND TX_COMN_MDL_MST.KIND_DTL_CD = TX_HRCH_MST.KIND_DTL_CD                 
               AND TRIM(TX_HRCH_MST.CAT_CD) = trim('VAIO')) A,               
               (select C.MDL_CD MDL_CD
                  from TA_MDL_SELL_IN_P C
                minus (select distinct A.MDL_CD
                        from TA_MDL_SELL_IN_P A,
                             (SELECT 'P' || A.MOD MDL_CD
                                FROM (SELECT SUBSTR(T.MDL_CD,
                                                    2,
                                                    LENGTH(T.MDL_CD) - 1) MOD
                                        FROM TA_MDL_SELL_IN_P T
                                       WHERE T.MDL_CD LIKE 'P%'
                                      INTERSECT
                                      SELECT SUBSTR(P.MDL_CD,
                                                    2,
                                                    22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   LENGTH(P.MDL_CD) - 1) MOD
                                        FROM TA_MDL_SELL_IN_P P
                                       WHERE P.MDL_CD LIKE 'N%') A
                              UNION
                              SELECT DISTINCT TA_MDL_DEF_FLG.MDL_CD
                                FROM TA_MDL_DEF_FLG
                               WHERE TA_MDL_DEF_FLG.DISCON_FLG = '1') B
                       where A.MDL_CD = B.MDL_CD)) D
         where A.mdl_cd = D.mdl_cd) E
GROUP BY E.MDL_CD, E.WEEK
ORDER BY E.MDL_CD, E.WEEK)
38   39   40   41   42   43   44   45   46   47   48  ;
  COUNT(*)
----------
     12516
Elapsed: 00:00:06.50

另外一条要等3个小时,我看不需要给结果在这里。

快在哪里呢? 不过是nest loop换HASH 而已。这条SQL统计信息其实已经分析过来,好像还是信息还是有点不对,这里不用管它。

也许还有别的提高方法,你们可以发上来,我来try. [/quote]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24492954/viewspace-692183/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 去除无用索引
请登录后发表评论 登录
全部评论

注册时间:2011-04-09

  • 博文量
    41
  • 访问量
    240850