ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 难以置信但却是事实的优化实例

难以置信但却是事实的优化实例

原创 Linux操作系统 作者:en-core 时间:2011-05-16 18:48:48 0 删除 编辑
下面是我们给客户公司优化过的一个实例,请大家比较一下优化前与优化后的时间

PERFORMANCE TUNING GUIDE

 

SITE

MCS

业务

 

顾问姓名

金某

开发负责

 

TYPE

  Batch

果确

 

填写日期

2010-03-23

化所需时间

 

化前时间

335.05

时间

0.857

 

SQL

原程序 CUR_2, CUR_3按照过程化的方式在执行

/* UPDATE  MONTH ACT TAR_STEPRESULT  */       

    CURSOR CUR_2 IS

    select 

           GBM, AREA, ITEM, MODELNAME, PAREA, STEP,

           to_number(sum(DLYIN))  CUMIN, 

           to_number(sum(DLYOUT)) CUMOUT

      from tar_STEPRESULT a,

           mst_time       b

     where a.yymmdd  = b.dateid 

       and a.yymmdd <= v_actdate

       and b.year||b.month = (select year||month from mst_time where dateid = v_actdate)

     group by GBM, AREA, ITEM, MODELNAME, PAREA, STEP

    ;

    

   CUR_REC2   CUR_2%ROWTYPE;    

    

    /* UPDATE  WEEK ACT TAR_STEPRESULT  */       

    CURSOR CUR_3 IS

    select 

           GBM, AREA, ITEM, MODELNAME, PAREA, STEP,

           to_number(sum(DLYIN))  WLYIN, 

           to_number(sum(DLYOUT)) WLYOUT

      from tar_STEPRESULT a,

           mst_time       b

     where a.yymmdd  = b.dateid 

       and a.yymmdd <= v_actdate

       and b.yearweek = (select yearweek from mst_time where dateid = v_actdate)

     group by GBM, AREA, ITEM, MODELNAME, PAREA, STEP

   ;

   

   CUR_REC3   CUR_3%ROWTYPE;

 

原程序 单独执行FETCH INSERT操作

FETCH SEL_CUR INTO v_SEL_CUR_REC;

       EXIT WHEN SEL_CUR%NOTFOUND;

 

        v_tempcnt   := 1;

        BEGIN

            INSERT INTO TAR_STEPRESULT

            (

               GBM,

               AREA,

               ITEM,

               MODELNAME,

               YYMMDD,

               PAREA,

               STEP,

               DLYBOH,

               CUMBOH,

               DLYIN,

               DLYOUT,

               WLYIN,

               WLYOUT,

               CUMIN,               

               CUMOUT,

               EOH,

               INITDTTM,

               INITBY

            )VALUES(

                  v_SEL_CUR_REC.GBM

                , v_SEL_CUR_REC.AREA

                , v_SEL_CUR_REC.PART_NO

                , v_SEL_CUR_REC.SALES_CODE

                , v_SEL_CUR_REC.YYMMDD

                , v_SEL_CUR_REC.P_AREA

                , v_SEL_CUR_REC.STEP

                , v_SEL_CUR_REC.DLYBOH

                , v_SEL_CUR_REC.CUMBOH

                , v_SEL_CUR_REC.DLYIN

                , v_SEL_CUR_REC.DLYOUT

                , v_SEL_CUR_REC.WLYIN

                , v_SEL_CUR_REC.WLYOUT

                , v_SEL_CUR_REC.CUMIN                

                , v_SEL_CUR_REC.CUMOUT

                , v_SEL_CUR_REC.EOH

                , sysdate

                , v_progname

            );

 

            v_insertcnt := v_insertcnt + 1;

 

            IF mod(v_insertcnt,1000) = 0 THEN

                commit;

                   DBMS_OUTPUT.PUT_LINE('commit');

            END IF;

 

1. 绑定变量

ACTDATE IN VARCHAR2 DEFAULT NULL

 

2. 问题及原因

   1) 为了记录日志内容, 单独执行INSERT语句。

   2) 插入数据后,分别以周和月为单位创建数据集合,并以行为单位执行修改操作- 发生大量RANDOM I/O(CUR_2, CUR_3 按照过程化的方式在执行)

    3) 按照过程化的方式处理海量数据  => 11,000,000行数据执行TABLE FULL SCAN

 

3. 解决方案

   1) ARRAY PROCESSING INSERT.

   2) 合并CUR_2, CUR_3后,利用ROWID执行UPDATE (ARRAY PROCESSING ) DIRECT ACCESS

   3) 利用PARALLEL(/*+ PARALLEL(A 8) */

 

4. 优化后的SQL

CURSOR合并

CURSOR CUR_I IS

SELECT  ROW_ID, CUMIN, CUMOUT, WLYIN, WLYOUT

FROM     (

         SELECT   /*+ PARALLEL_INDEX(B PK_TAR_STEPRESULT 8) INDEX_FFS(B PK_TAR_STEPRESULT) USE_HASH(A B) */ 

                  A.GBM, A.AREA, A.ITEM, A.MODELNAME, A.PAREA, A.STEP, A.CUMIN, a.CUMOUT, A.WLYIN, A.WLYOUT, B.ROWID ROW_ID

                  FROM     (

                           SELECT   GBM, AREA, ITEM, MODELNAME, PAREA, STEP,

                                            SUM(CUMIN) CUMIN,  

                                            SUM(CUMOUT) CUMOUT,

                                            SUM(WLYIN) WLYIN,

                                            SUM(WLYOUT) WLYOUT

                           FROM     (

                                            SELECT   /*+ PARALLEL(A 8) PQ_DISTRIBUTE(A BROADCAST, NONE) */

                                                              GBM, AREA, ITEM, MODELNAME, PAREA, STEP,

                                                              TO_NUMBER(SUM(DLYIN))  CUMIN, 

                                                              TO_NUMBER(SUM(DLYOUT)) CUMOUT,

                                                              NULL WLYIN,

                                                              NULL WLYOUT

                                            FROM     TAR_STEPRESULT A,

                                                              MST_TIME       B

                                            WHERE    A.YYMMDD  = B.DATEID 

                                            AND      A.YYMMDD <= V_ACTDATE

                                            AND      B.YEAR||B.MONTH = (SELECT YEAR||MONTH FROM MST_TIME WHERE DATEID =V_ACTDATE) 

                                            GROUP BY GBM, AREA, ITEM, MODELNAME, PAREA, STEP

                           UNION ALL     

                           SELECT   /*+ PARALLEL(A 8) PQ_DISTRIBUTE(A BROADCAST, NONE) */

                                            GBM, AREA, ITEM, MODELNAME, PAREA, STEP,

                                            NULL, NULL,

                                            TO_NUMBER(SUM(DLYIN))  WLYIN, 

                                            TO_NUMBER(SUM(DLYOUT)) WLYOUT

                           FROM     TAR_STEPRESULT A,

                                            MST_TIME       B

                           WHERE    A.YYMMDD  = B.DATEID 

                           AND      A.YYMMDD <= V_ACTDATE

                           AND      B.YEARWEEK = (SELECT YEARWEEK FROM MST_TIME WHERE DATEID =V_ACTDATE) 

                           GROUP BY GBM, AREA, ITEM, MODELNAME, PAREA, STEP

                           )

         GROUP BY  GBM, AREA, ITEM, MODELNAME, PAREA, STEP

         ) A, TAR_STEPRESULT B

WHERE    A.GBM = B.GBM

  AND    A.AREA = B.AREA

  AND    A.ITEM = B.ITEM

  AND    A.MODELNAME = B.MODELNAME

  AND    A.PAREA = B.PAREA

  AND    A.STEP = B.STEP

  AND    B.YYMMDD =V_ACTDATE

)

;

ARRAY PROCESSING

FETCH    SEL_CUR BULK COLLECT 

INTO     l_gbm , l_area, l_item , l_modelname , l_yymmdd , l_parea , l_step

                  , l_dlyboh, l_cumboh, l_dlyin , l_dlyout, l_wlyin , l_wlyout , l_cumin , l_cumout, l_eoh

LIMIT    c_batch;

 

BEGIN

 

FORALL i IN l_gbm.FIRST .. l_gbm.LAST

SAVE EXCEPTIONS

 

         INSERT INTO TAR_STEPRESULT

         (

            GBM,

            AREA,

            ITEM,

            MODELNAME,

            YYMMDD,

            PAREA,

            STEP,

            DLYBOH,

            CUMBOH,

            DLYIN,

            DLYOUT,

            WLYIN,

            WLYOUT,

            CUMIN,               

            CUMOUT,

            EOH,

            INITDTTM,

            INITBY

         )VALUES(

                    l_gbm(i)

                  , l_area(i)

                  , l_item(i)

                  , l_modelname(i)

                  , l_yymmdd(i)

                  , l_parea(i)

                  , l_step(i)

                  , l_dlyboh(i)

                  , l_cumboh(i)

                  , l_dlyin(i)

                  , l_dlyout(i)

                  , l_wlyin(i)

                  , l_wlyout(i)

                  , l_cumin(i)                

                  , l_cumout(i)

                  , l_eoh(i)

                  , SYSDATE

                  , V_PROGNAME

         );

 

         v_insertcnt := v_insertcnt + SQL%ROWCOUNT;

 

EXCEPTION

         WHEN bulk_errors THEN

                  FOR indx IN 1..SQL%BULK_EXCEPTIONS.COUNT

                  LOOP

                           v_errmessage := SUBSTR(SQLERRM(-1*SQL%BULK_EXCEPTIONS(indx).ERROR_CODE)

                                   ||' ERROR_DATA('

                                   ||' GBM        ' || l_gbm(SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX)

                                   ||' AREA       ' || l_area(SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX)

                                   ||' PART_NO    ' || l_item(SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX)

                                   ||' SALES_CODE ' || l_modelname(SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX)

                                   ||' YYMMDD     ' || l_yymmdd(SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX)

                                   ||' P_AREA     ' || l_parea(SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX)

                                   ||' STEP       ' || l_step(SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX)

                                   ||')',1,2000); -- errormessage

                           SP_LOGGING_DETAIL_REG(v_progname,v_startdate,v_errmessage,v_logseq);

                           v_errorcnt := v_errorcnt+1;

                  END LOOP;

END;

 

5. 性能比较

1) 执行计划

优化前  - CUR_2

--------------------------------------------------------------------------------------------------

| Id  | Operation                      | Name           | Starts | E-Rows |   A-Time   | Buffers |

--------------------------------------------------------------------------------------------------

|   1 |  HASH GROUP BY                 |                |      1 |   6908 |00:00:10.79 |   93421 |

|*  2 |   HASH JOIN                    |                |      1 |   6908 |00:00:07.67 |   93421 |

|*  3 |    TABLE ACCESS BY INDEX ROWID | MST_TIME       |      1 |      2 |00:00:00.01 |      37 |

|*  4 |     INDEX RANGE SCAN           | MST_TIME_PK    |      1 |     33 |00:00:00.01 |       6 |

|   5 |     TABLE ACCESS BY INDEX ROWID| MST_TIME       |      1 |      1 |00:00:00.01 |       3 |

|*  6 |      INDEX UNIQUE SCAN         | MST_TIME_PK    |      1 |      1 |00:00:00.01 |       2 |

|*  7 |    TABLE ACCESS FULL           | TAR_STEPRESULT |      1 |    514K|00:00:00.01 |   93384 |

--------------------------------------------------------------------------------------------------

 

 

优化前  - CUR_3

 

---------------------------------------------------------------------------------------------------

| Id  | Operation                      | Name           | Starts | E-Rows |   A-Time   | Buffers |

--------------------------------------------------------------------------------------------------

|   1 |  HASH GROUP BY                 |                |      1 |   1323 |00:00:08.18 |   93421 |

|*  2 |   HASH JOIN                    |                |      1 |   1323 |00:00:07.69 |   93421 |

|*  3 |    TABLE ACCESS BY INDEX ROWID | MST_TIME       |      1 |      1 |00:00:00.01 |      37 |

|*  4 |     INDEX RANGE SCAN           | MST_TIME_PK    |      1 |     33 |00:00:00.01 |       6 |

|   5 |     TABLE ACCESS BY INDEX ROWID| MST_TIME       |      1 |      1 |00:00:00.01 |       3 |

|*  6 |      INDEX UNIQUE SCAN         | MST_TIME_PK    |      1 |      1 |00:00:00.01 |       2 |

|*  7 |    TABLE ACCESS FULL           | TAR_STEPRESULT |      1 |    514K|00:00:00.01 |   93384 |

--------------------------------------------------------------------------------------------------

 

 

优化后  - 合并

 

-----------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                        | Name                    | Starts | E-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------------------------------------------------------

|   1 |  PX COORDINATOR                                  |                         |      1 |        |00:00:03.41 |   48593 |

|   2 |   PX SEND QC (RANDOM)                            | :TQ10007                |      0 |    147 |00:00:00.01 |       0 |

|*  3 |    HASH JOIN                                     |                         |      0 |    147 |00:00:00.01 |       0 |

|   4 |     PX RECEIVE                                   |                         |      0 |  22307 |00:00:00.01 |       0 |

|   5 |      PX SEND HASH                                | :TQ10006                |      0 |  22307 |00:00:00.01 |       0 |

|   6 |       VIEW                                       |                         |      0 |  22307 |00:00:00.01 |       0 |

|   7 |        HASH GROUP BY                             |                         |      0 |  22307 |00:00:00.01 |       0 |

|   8 |         PX RECEIVE                               |                         |      0 |  22307 |00:00:00.01 |       0 |

|   9 |          PX SEND HASH                            | :TQ10005                |      0 |  22307 |00:00:00.01 |       0 |

|  10 |           HASH GROUP BY                          |                         |      0 |  22307 |00:00:00.01 |       0 |

|  11 |            VIEW                                  |                         |      0 |  22307 |00:00:00.01 |       0 |

|  12 |             UNION-ALL                            |                         |      0 |        |00:00:00.01 |       0 |

|  13 |              HASH GROUP BY                       |                         |      0 |  18721 |00:00:00.01 |       0 |

|  14 |               PX RECEIVE                         |                         |      0 |  18721 |00:00:00.01 |       0 |

|  15 |                PX SEND HASH                      | :TQ10003                |      0 |  18721 |00:00:00.01 |       0 |

|  16 |                 HASH GROUP BY                    |                         |      0 |  18721 |00:00:00.01 |       0 |

|* 17 |                  HASH JOIN                       |                         |      0 |  18721 |00:00:00.01 |       0 |

|  18 |                   BUFFER SORT                    |                         |      0 |        |00:00:00.01 |       0 |

|  19 |                    PX RECEIVE                    |                         |      0 |      2 |00:00:00.01 |       0 |

|  20 |                     PX SEND BROADCAST            | :TQ10000                |      0 |      2 |00:00:00.01 |       0 |

|* 21 |                      TABLE ACCESS BY INDEX ROWID | MST_TIME                |      1 |      2 |00:00:00.01 |       0 |

|* 22 |                       INDEX RANGE SCAN           | MST_TIME_PK             |      1 |     33 |00:00:00.01 |       0 |

|  23 |                       TABLE ACCESS BY INDEX ROWID| MST_TIME                |      0 |      1 |00:00:00.01 |       0 |

|* 24 |                        INDEX UNIQUE SCAN         | MST_TIME_PK             |      0 |      1 |00:00:00.01 |       0 |

|  25 |                   PX BLOCK ITERATOR              |                         |      0 |    502K|00:00:00.01 |       0 |

|* 26 |                    TABLE ACCESS FULL             | ENC_TAR_STEPRESULT_2    |      0 |    502K|00:00:00.01 |       0 |

|  27 |              HASH GROUP BY                       |                         |      0 |   3586 |00:00:00.01 |       0 |

|  28 |               PX RECEIVE                         |                         |      0 |   3586 |00:00:00.01 |       0 |

|  29 |                PX SEND HASH                      | :TQ10004                |      0 |   3586 |00:00:00.01 |       0 |

|  30 |                 HASH GROUP BY                    |                         |      0 |   3586 |00:00:00.01 |       0 |

|* 31 |                  HASH JOIN                       |                         |      0 |   3586 |00:00:00.01 |       0 |

|  32 |                   BUFFER SORT                    |                         |      0 |        |00:00:00.01 |       0 |

|  33 |                    PX RECEIVE                    |                         |      0 |      1 |00:00:00.01 |       0 |

|  34 |                     PX SEND BROADCAST            | :TQ10001                |      0 |      1 |00:00:00.01 |       0 |

|* 35 |                      TABLE ACCESS BY INDEX ROWID | MST_TIME                |      1 |      1 |00:00:00.01 |       0 |

|* 36 |                       INDEX RANGE SCAN           | MST_TIME_PK             |      1 |     33 |00:00:00.01 |       0 |

|  37 |                       TABLE ACCESS BY INDEX ROWID| MST_TIME                |      0 |      1 |00:00:00.01 |       0 |

|* 38 |                        INDEX UNIQUE SCAN         | MST_TIME_PK             |      0 |      1 |00:00:00.01 |       0 |

|  39 |                   PX BLOCK ITERATOR              |                         |      0 |    502K|00:00:00.01 |       0 |

|* 40 |                    TABLE ACCESS FULL             | ENC_TAR_STEPRESULT_2    |      0 |    502K|00:00:00.01 |       0 |

|  41 |     BUFFER SORT                                  |                         |      0 |        |00:00:00.01 |       0 |

|  42 |      PX RECEIVE                                  |                         |      0 |    100K|00:00:00.01 |       0 |

|  43 |       PX SEND HASH                               | :TQ10002                |      0 |    100K|00:00:00.01 |       0 |

|* 44 |        INDEX FAST FULL SCAN                      | PK_ENC_TAR_STEPRESULT_2 |      1 |    100K|00:00:03.31 |   48587 |

-----------------------------------------------------------------------------------------------------------------------------

2) 执行时间

区分

优化CURSOR

ARRAY SIZE

执行时间()

优化前

335.05

优化后

应用

100

38.80

500

34.58

1000

19.85

10000

19.37

 

 

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

请登录后发表评论 登录
全部评论

注册时间:2011-04-18

  • 博文量
    11
  • 访问量
    12057