生產系統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
        ( 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, 0, 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);  
            
    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的改造

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, 0, subscriber.effective_date, subscriber.eff_issue_date, subscriber.expiration_date,subscriber.ins_trx_id, 'PARAM2',
           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='Rolled ATB quota from ensemble');

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