生産系統pl/sql調優案例

昨天基本休息了一天，想着生産系統更新也會多多少少碰到些問題，肯定有一些心得或者是值得學習的東西，結果昨晚到現在生産系統更新一直為一個pl/sql的問題所困擾。在測試環境中隻用了十多分鐘， 在生産系統上跑了快5個小時。這個經曆太痛苦了，大半夜還在考慮怎麼優化真是痛苦。

這個也算是一個很深刻的學習經驗，和大家分享一下。

pl/sql的大體功能是從使用者訂購的套餐根據指定的參數來取得所對應的産品編号，然後在訂購表中去查詢，生成動态的sql語句。看起來功能也不複雜。代碼如下:

首先按照要求清除指定的資料，然後在兩個循環中去動态的insert。這種實作可能是大家都會使用的一般方式。

delete /*+ parallel( HUGE_PARAMS,8)*/ HUGE_PARAMS where param_name in

(

'PARAM1',

'PARAM2',

'PARAM3',

'PARAM4',

'PARAM5',

'PARAM6');

COMMIT;

declare

    seq_no number(9);

  begin

//根據條件取得相應的産品編号，輸出大概有4000條左右。

  for params in (

                 select distinct param_name,offer_code

                 from  OFFER_PARAM where param_name in

        (

        'PARAM1',

        'PARAM2',

        'PARAM3',

        'PARAM4',

        'PARAM5',

        'PARAM6');     

//在此基礎上進行疊代循環，根據取得的産品編号，和一個大表關聯，生成insert語句。    HUGE_DATA有大概2000萬的資料，而且查詢條件沒有主鍵關聯。               

  loop

    Dbms_Output.Put_Line ('Parameters:' || params.param_name );

    for subscriber in (

                      select xxxxxx

                        from HUGE_DATA

                       where offer_code = params.offer_code                      

                    )

    loop

      Dbms_Output.Put_Line ('Subscriber:' || .........);

    select  HUGE_PARAMS_sq.nextval into seq_no from dual;

//對于參數1，insert語句有一些變化，對于其他的參數，insert的格式都基本一緻。HUGE_PARAMS裡面有近2000萬條記錄。    

    IF params.param_name='PARAM1'

    THEN

    INSERT /*+ parallel( HUGE_PARAMS,4) */INTO  HUGE_PARAMS

        ( HUGE_PARAMS.AGR_LEVEL,  HUGE_PARAMS.EXP_ISSUE_DATE,  HUGE_PARAMS.PARAM_VALUES,  HUGE_PARAMS.EFFECTIVE_DATE,

         HUGE_PARAMS.EFF_ISSUE_DATE,  HUGE_PARAMS.EXPIRATION_DATE,  HUGE_PARAMS.INS_TRX_ID,  HUGE_PARAMS.PARAM_NAME,

         HUGE_PARAMS.AGREEMENT_NO,  HUGE_PARAMS.AGREEMENT_KEY,  HUGE_PARAMS.TRX_ID,  HUGE_PARAMS.OFFER_INSTANCE_ID,

         HUGE_PARAMS.PARAM_SEQ_NO, OPERATOR_ID, APPLICATION_ID, DL_SERVICE_CODE, DL_UPDATE_STAMP, SYS_CREATION_DATE, SYS_UPDATE_DATE)

    VALUES

    ('S', subscriber.exp_issue_date, 'N', subscriber.effective_date, subscriber.eff_issue_date, subscriber.expiration_date,subscriber.ins_trx_id, params.param_name,

           subscriber.agreement_no, subscriber.agreement_key, subscriber.trx_id, subscriber.soc_seq_no, seq_no, subscriber.operator_id, subscriber.application_id,

           subscriber.dl_service_code, subscriber.dl_update_stamp, subscriber.sys_creation_date, NULL); 

    ELSE

    INSERT /*+ parallel( HUGE_PARAMS,4) */ INTO  HUGE_PARAMS

    ('S', subscriber.exp_issue_date, 0, subscriber.effective_date, subscriber.eff_issue_date, subscriber.expiration_date,subscriber.ins_trx_id, params.param_name,

           subscriber.dl_service_code, subscriber.dl_update_stamp, subscriber.sys_creation_date, NULL);  

    END IF;            

    end loop;

//在子循環後，進行commit

    COMMIT;

  end loop;

end;

/

結果等了很久，開發和我們的壓力都很大。 大家就試着想想做一個預備方案，看能不能優化一下。

首先的思路就是拆分，能盡量去除循環。

然後嘗試把insert ,values的方式改造成insert select的形式。

這樣不論需要生成幾千幾萬的insert,values語句，insert,select的形式隻需要幾個單獨的sql語句。

最後在一個臨時的空表中進行測試，發現執行隻需要不到一分鐘。在開發進行了資料的檢查後，和期望的一樣，資料條數也絲毫不差。

//對于PARAM1的語句，标黃的部分就是有差别的地方。其餘部分PARAM2,3,4,5,6都是類似的格式。

###PARAM1的改造

INSERT /*+ parallel(HUGE_PARAMS,4) */INTO HUGE_PARAMS

        (AGR_LEVEL, EXP_ISSUE_DATE, PARAM_VALUES, EFFECTIVE_DATE,

        EFF_ISSUE_DATE, EXPIRATION_DATE, INS_TRX_ID, PARAM_NAME,

        AGREEMENT_NO, AGREEMENT_KEY, TRX_ID, OFFER_INSTANCE_ID,

        PARAM_SEQ_NO, OPERATOR_ID, APPLICATION_ID, DL_SERVICE_CODE, DL_UPDATE_STAMP, SYS_CREATION_DATE, SYS_UPDATE_DATE)

    select

    'S', subscriber.exp_issue_date, 'N', subscriber.effective_date, subscriber.eff_issue_date, subscriber.expiration_date,subscriber.ins_trx_id, ’PARAM1',

           subscriber.agreement_no, mod(subscriber.agreement_no,100), subscriber.trx_id, subscriber.soc_seq_no, HUGE_PARAMS_sq.nextval,subscriber.operator_id, subscriber.application_id,

           subscriber.dl_service_code, subscriber.dl_update_stamp, subscriber.sys_creation_date, NULL from service_agreement  subscriber

                       where soc in (select distinct soc_cd from OFFER_PARAM where param_name='');

###PARAM2，3，4，5，6的改造

    'S', subscriber.exp_issue_date, 0, subscriber.effective_date, subscriber.eff_issue_date, subscriber.expiration_date,subscriber.ins_trx_id, 'PARAM2',

                       where soc in (select distinct soc_cd from OFFER_PARAM where param_name='Rolled ATB quota from ensemble');

從pl/sql改造成sql的方式也是根據業務來考慮的。歡迎拍磚。