谈Oracle存储过程优化，有兴趣的进人人有分。

Question

案例：描述：1.从用户产品表关联产品价格表查询出截止当月20号正常使用产品的用户以及产品、产品价格
      2.还需要关联一张用户表去获得用户所在地市编码
      2.将获得的数据一次性插入短信表数量级：用户产品表TF_F_USER_SP  900W数据
        价格表：6000数据
       用户表：9000W数据最后获取满足条件的数据：300W条过程源代码：create or replace procedure P_SMS_SENDINFOTOUSER(V_RESULTCODE OUT NUMBER,
                                                      V_RESULTINFO OUT VARCHAR2) IS
  TYPE T_CURSOR IS REF CURSOR;  IV_NOTICE_CONTENT TI_O_SMS.NOTICE_CONTENT%TYPE; --短信内容
  IV_EPARCHY_CODE   TF_F_USER.EPARCHY_CODE%TYPE; --地市编码
  --定制生效的SP产品
  IV_CURSOR        T_CURSOR;
  IV_SERIAL_NUMBER TF_F_USER_SP.SERIAL_NUMBER%TYPE; --手机号码
  IV_USER_ID         TF_F_USER_SP.USER_ID%TYPE; --用户标识
  IV_SP_ID           TF_F_USER_SP.SP_ID%TYPE; 
  IV_SP_PRODUCT_ID   TD_B_PARTY_PRODUCT.SP_PRODUCT_ID%TYPE;
  IV_SP_PRODUCT_NAME TD_B_PARTY_PRODUCT.SP_PRODUCT_NAME%TYPE; -- SP名称
  IV_USER_SP_ALL     VARCHAR2(500); --用户所有的SP
  IV_COUNT           NUMBER(10);
  IV_PRICE           VARCHAR(20); --价格
  IV_USE_COUNT       NUMBER(10); --记录用户个数
  IV_CU_COUNT        NUMBER(10); --记录游标存储记录数
  IV_IS_HAS_M        NUMBER(1);--产品费用中是否含有M  0：表示不含有 1反之
  
  
  IV_SERIAL_NUMBER_TEMP TF_F_USER_SP.SERIAL_NUMBER%TYPE;
  IV_USER_ID_TEMP       TF_F_USER_SP.USER_ID%TYPE;BEGIN
  V_RESULTCODE    := -1;
  V_RESULTINFO    := 0;
  IV_EPARCHY_CODE := '';
  BEGIN
  
    IV_SERIAL_NUMBER_TEMP := '-1';
    IV_USER_ID_TEMP       := '';
    IV_USER_SP_ALL        := '';
    IV_COUNT              := 0;
    IV_PRICE              := '0';
    IV_USE_COUNT          := 0;
    IV_CU_COUNT           := 0;
    IV_IS_HAS_M           := 0;    INSERT INTO ucr_crm1.fx_TEMP_TF_F_USER_SP_20130420
            SELECT A.SERIAL_NUMBER,
             A.USER_ID,
             (SELECT B.EPARCHY_CODE
                FROM TF_F_USER B
               WHERE B.SERIAL_NUMBER = A.SERIAL_NUMBER
                 AND B.EPARCHY_CODE IS NOT NULL
                 AND ROWNUM = 1) EPARCHY_CODE,
             A.SP_ID,
             A.SP_PRODUCT_ID
        FROM TF_F_USER_SP A, TD_B_PARTY_PRICE C
       WHERE A.SP_PRODUCT_ID = C.PRODUCTCODE
         AND C.BILLINGMODECODE = 'OrderMonth'
         AND A.START_DATE < SYSDATE
         AND A.END_DATE > SYSDATE;
        COMMIT;
         
    OPEN IV_CURSOR FOR
         SELECT A.SERIAL_NUMBER,
             A.USER_ID,
             A.EPARCHY_CODE,
             A.SP_ID,
             A.SP_PRODUCT_ID
        FROM ucr_crm1.fx_TEMP_TF_F_USER_SP_20130420 A
       ORDER BY A.SERIAL_NUMBER;
  
    LOOP
      FETCH IV_CURSOR
        INTO IV_SERIAL_NUMBER,
             IV_USER_ID,
             IV_EPARCHY_CODE,
             IV_SP_ID,
             IV_SP_PRODUCT_ID;
      IF IV_CURSOR%NOTFOUND THEN
        --IV_CU_COUNT := IV_CURSOR%ROWCOUNT;
        IV_SERIAL_NUMBER := '0';
      END IF;
      IF IV_EPARCHY_CODE IS NULL THEN
        IV_EPARCHY_CODE := 'ZZZZ';
      END IF;
      IF (IV_SERIAL_NUMBER_TEMP <> IV_SERIAL_NUMBER AND
         IV_SERIAL_NUMBER_TEMP <> '-1') OR LENGTHB(IV_USER_SP_ALL)>=400 THEN
        BEGIN
          -- 先发送上一个用户的短信。
          IV_NOTICE_CONTENT := '温馨提示：您当月已订购以下增值产品:' || IV_USER_SP_ALL ||
                               '您可拨打10010查询和退订。';
        
          INSERT INTO TI_O_SMS
            (SMS_NOTICE_ID,
             EPARCHY_CODE,
             IN_MODE_CODE,
             SMS_CHANNEL_CODE,
             RECV_OBJECT_TYPE,
             RECV_OBJECT,
             ID,
             SMS_TYPE_CODE,
             SMS_KIND_CODE,
             NOTICE_CONTENT_TYPE,
             NOTICE_CONTENT,
             FORCE_REFER_COUNT,
             SMS_PRIORITY,
             REFER_TIME,
             REFER_STAFF_ID,
             REFER_DEPART_ID,
             DEAL_TIME,
             DEAL_STATE,
             REMARK,
             SEND_TIME_CODE,
             SEND_OBJECT_CODE)
            SELECT TO_NUMBER(F_SYS_GETSEQID(IV_EPARCHY_CODE,
                                            'seq_smssend_id')),
                   IV_EPARCHY_CODE,
                   '0',
                   '11', --短信渠道编码:客户服务
                   '00', --被叫对象类型:00－手机号码
                   IV_SERIAL_NUMBER_TEMP, --被叫对象:传手机号码
                   NVL(TO_NUMBER(IV_USER_ID_TEMP), 0), --被叫对象标识:传用户标识
                   '20', --短信类型:20-业务通知
                   '11', --短信种类:02－短信通知
                   '0', --短信内容类型:0－指定内容发送
                   IV_NOTICE_CONTENT, --短信内容
                   1, --指定发送次数
                   50, --短信优先级
                   SYSDATE, --提交时间
                   '',
                   '',
                   SYSDATE, --处理时间
                   '0', --处理状态:0－未处理
                   '定制业务信息的短信提醒', --备注
                   '2',
                   1
              FROM DUAL;
          V_RESULTINFO := '成功插入短信接口表';
          COMMIT;
          IV_USER_SP_ALL := '';
          IV_COUNT       := 0;
        END;
      END IF;
      EXIT WHEN IV_CURSOR%NOTFOUND;
    
      SELECT DISTINCT A.SP_PRODUCT_NAME
        INTO IV_SP_PRODUCT_NAME
        FROM TD_B_PARTY_PRODUCT A
       WHERE A.SP_ID = IV_SP_ID
         AND A.SP_PRODUCT_ID = IV_SP_PRODUCT_ID;
      
      SELECT DECODE(DATA_TYPE,'NUMBER',to_char(FEE/100,'FM9999999999990.90')||'元/月',fee||'元/月') PRICE
      INTO IV_PRICE
      FROM (SELECT   a.productcode,a.productname,
                     nvl2(translate(SUBSTR(a.paravalue,5,INSTR(A.PARAVALUE,';')-5), '\1234567890', '\'), 'CHAR', 'NUMBER') DATA_TYPE,
           CASE WHEN nvl2(translate(SUBSTR(a.paravalue,5,INSTR(A.PARAVALUE,';')-5), '\1234567890', '\'), 'CHAR', 'NUMBER') ='CHAR' 
                THEN SUBSTR(SUBSTR(a.paravalue,5,INSTR(A.PARAVALUE,';')-5),1,REGEXP_INSTR(SUBSTR(a.paravalue,5,INSTR(A.PARAVALUE,';')-5),'[^0-9]')-1)
                ELSE TO_CHAR(NVL(SUBSTR(a.paravalue,5,INSTR(A.PARAVALUE,';')-5),0))  END  FEE
      FROM td_b_party_price a WHERE a.productcode = IV_SP_PRODUCT_ID AND a.billingmodecode ='OrderMonth'
      ) T;
                           
        
      IF IV_COUNT = 0 THEN
        IV_USER_SP_ALL := IV_SP_PRODUCT_NAME || '业务、资费' || IV_PRICE ||
                          '；';
      ELSE
        IV_USER_SP_ALL := IV_USER_SP_ALL || ' ' || IV_SP_PRODUCT_NAME ||
                          '业务、资费' || IV_PRICE || '；';
      END IF;
    
      IV_COUNT     := IV_COUNT + 1;
      IV_USE_COUNT := IV_USE_COUNT + 1;
      --IF IV_USE_Count = IV_CU_COUNT THEN
      --IV_SERIAL_NUMBER_TEMP := '0';
      --ELSE 
      IV_SERIAL_NUMBER_TEMP := IV_SERIAL_NUMBER;
      --END IF;
      --IV_SERIAL_NUMBER_TEMP := IV_SERIAL_NUMBER;
      IV_USER_ID_TEMP := IV_USER_ID;
    
    END LOOP;
    CLOSE IV_CURSOR;
    DELETE ucr_crm1.fx_TEMP_TF_F_USER_SP_20130420;
    COMMIT;
  
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      V_RESULTINFO := '定制生效的套餐发短信提醒报错:' || SQLERRM;
      V_RESULTCODE := -1;
  END;
  V_RESULTCODE := 0;
  V_RESULTINFO := 'trade OK！';
end P_SMS_SENDINFOTOUSER;
/我在跑存储过程的过程中执行到70分钟的时候 PL/SQL无响应，不知存储过程依然在跑?烦请说说优化方案；如有问题及时提出，在线等。谢谢!!

Answer 1

看了你的存储过程，提以下几点疑问与建议：
    1、游标中去掉order by 排序；
    2、查询条件中是否建了索引?
　　3、异常中事务回滚存在脏数据；
    4、分解存储过程中语句，分析瓶颈、优化；
    5、此存储过程是否考虑并发?

Answer 2

SQL语句可能不能在拆分了。
远程连接数据库执行存储过程的请问PLSQL执行完一半数据 卡死了  不执行了 但是不回滚也就是没有遇到异常。什么情况啊

Answer 3

关于查询 已经在20分钟前执行完成 
现在问题出在了 LOOP 插入表数据上 当插入21W数据的时候 PLSQL卡死无响应。

Answer 4

你的COMMIT命令不要写在前面。。你写在存储过程的最后面，这样才有事务的作用。
发现你不要用Order By 语句，你的数据量本来就大，你还用Order By语句的话，会更加减弱查询的效率。
还有你需要优化一下查询语句。个人觉得你在插入的时候不要一下子操作几百万条记录。可以分批插入。

Answer 5

查询已经完成了的，问题是出现在 LOOP中  一次循环只是插入一条数据。

Answer 6

create or replace procedure P_SMS_SENDINFOTOUSER(V_RESULTCODE OUT NUMBER,
                                                 V_RESULTINFO OUT VARCHAR2) IS
  TYPE T_CURSOR IS REF CURSOR;  IV_NOTICE_CONTENT TI_O_SMS.NOTICE_CONTENT%TYPE; --短信内容
  IV_EPARCHY_CODE   TF_F_USER.EPARCHY_CODE%TYPE; --地市编码
  --定制生效的SP产品
  IV_CURSOR          T_CURSOR;
  IV_SERIAL_NUMBER   TF_F_USER_SP.SERIAL_NUMBER%TYPE; --手机号码
  IV_USER_ID         TF_F_USER_SP.USER_ID%TYPE; --用户标识
  IV_SP_ID           TF_F_USER_SP.SP_ID%TYPE;
  IV_SP_PRODUCT_ID   TD_B_PARTY_PRODUCT.SP_PRODUCT_ID%TYPE;
  IV_SP_PRODUCT_NAME TD_B_PARTY_PRODUCT.SP_PRODUCT_NAME%TYPE; -- SP名称
  IV_USER_SP_ALL     VARCHAR2(500); --用户所有的SP
  IV_COUNT           NUMBER(10);
  IV_PRICE           VARCHAR(20); --价格
  IV_USE_COUNT       NUMBER(10); --记录用户个数
  IV_CU_COUNT        NUMBER(10); --记录游标存储记录数
  IV_IS_HAS_M        NUMBER(1); --产品费用中是否含有M  0：表示不含有 1反之  IV_SERIAL_NUMBER_TEMP TF_F_USER_SP.SERIAL_NUMBER%TYPE;
  IV_USER_ID_TEMP       TF_F_USER_SP.USER_ID%TYPE;BEGIN
  V_RESULTCODE    := -1;
  V_RESULTINFO    := 0;
  IV_EPARCHY_CODE := '';
  BEGIN
  
    IV_SERIAL_NUMBER_TEMP := '-1';
    IV_USER_ID_TEMP       := '';
    IV_USER_SP_ALL        := '';
    IV_COUNT              := 0;
    IV_PRICE              := '0';
    IV_USE_COUNT          := 0;
    IV_CU_COUNT           := 0;
    IV_IS_HAS_M           := 0;
    --将fx_TEMP_TF_F_USER_SP_20130420  改为临时表
    --下面查询语句可以拿出来进行优化、耗时分析
    INSERT INTO ucr_crm1.fx_TEMP_TF_F_USER_SP_20130420
      SELECT A.SERIAL_NUMBER,
             A.USER_ID,
             (SELECT B.EPARCHY_CODE
                FROM TF_F_USER B
               WHERE B.SERIAL_NUMBER = A.SERIAL_NUMBER
                 AND B.EPARCHY_CODE IS NOT NULL
                 AND ROWNUM = 1) EPARCHY_CODE,
             A.SP_ID,
             A.SP_PRODUCT_ID
        FROM TF_F_USER_SP A, TD_B_PARTY_PRICE C
       WHERE A.SP_PRODUCT_ID = C.PRODUCTCODE
         AND C.BILLINGMODECODE = 'OrderMonth'
         AND A.START_DATE < SYSDATE
         AND A.END_DATE > SYSDATE;
    --COMMIT;  去掉
  
    OPEN IV_CURSOR FOR
      SELECT A.SERIAL_NUMBER,
             A.USER_ID,
             --A.EPARCHY_CODE,
             nvl(A.EPARCHY_CODE,'ZZZZ') EPARCHY_CODE,
             A.SP_ID,
             A.SP_PRODUCT_ID
        FROM ucr_crm1.fx_TEMP_TF_F_USER_SP_20130420 A;
       --ORDER BY A.SERIAL_NUMBER; 去掉
  
    LOOP
      FETCH IV_CURSOR
        INTO IV_SERIAL_NUMBER,
             IV_USER_ID,
             IV_EPARCHY_CODE,
             IV_SP_ID,
             IV_SP_PRODUCT_ID;
      IF IV_CURSOR%NOTFOUND THEN
        --IV_CU_COUNT := IV_CURSOR%ROWCOUNT;
        IV_SERIAL_NUMBER := '0';
      END IF;
      /*IF IV_EPARCHY_CODE IS NULL THEN
         IV_EPARCHY_CODE := 'ZZZZ';
      END IF;*/
      IF (IV_SERIAL_NUMBER_TEMP <> IV_SERIAL_NUMBER AND
         IV_SERIAL_NUMBER_TEMP <> '-1') OR LENGTHB(IV_USER_SP_ALL) >= 400 THEN
        BEGIN
          -- 先发送上一个用户的短信。
          IV_NOTICE_CONTENT := '温馨提示：您当月已订购以下增值产品:' || IV_USER_SP_ALL ||
                               '您可拨打10010查询和退订。';
        
          INSERT INTO TI_O_SMS
            (SMS_NOTICE_ID,
             EPARCHY_CODE,
             IN_MODE_CODE,
             SMS_CHANNEL_CODE,
             RECV_OBJECT_TYPE,
             RECV_OBJECT,
             ID,
             SMS_TYPE_CODE,
             SMS_KIND_CODE,
             NOTICE_CONTENT_TYPE,
             NOTICE_CONTENT,
             FORCE_REFER_COUNT,
             SMS_PRIORITY,
             REFER_TIME,
             REFER_STAFF_ID,
             REFER_DEPART_ID,
             DEAL_TIME,
             DEAL_STATE,
             REMARK,
             SEND_TIME_CODE,
             SEND_OBJECT_CODE)
             --这里按值插入是否消耗小一些
            SELECT TO_NUMBER(F_SYS_GETSEQID(IV_EPARCHY_CODE,
                                            'seq_smssend_id')),
                   IV_EPARCHY_CODE,
                   '0',
                   '11', --短信渠道编码:客户服务
                   '00', --被叫对象类型:00－手机号码
                   IV_SERIAL_NUMBER_TEMP, --被叫对象:传手机号码
                   NVL(TO_NUMBER(IV_USER_ID_TEMP), 0), --被叫对象标识:传用户标识
                   '20', --短信类型:20-业务通知
                   '11', --短信种类:02－短信通知
                   '0', --短信内容类型:0－指定内容发送
                   IV_NOTICE_CONTENT, --短信内容
                   1, --指定发送次数
                   50, --短信优先级
                   SYSDATE, --提交时间
                   '',
                   '',
                   SYSDATE, --处理时间
                   '0', --处理状态:0－未处理
                   '定制业务信息的短信提醒', --备注
                   '2',
                   1
              FROM DUAL;
          V_RESULTINFO := '成功插入短信接口表';
          --COMMIT;  去掉
          IV_USER_SP_ALL := '';
          IV_COUNT       := 0;
        END;
      END IF;
      EXIT WHEN IV_CURSOR%NOTFOUND;
    
      SELECT DISTINCT A.SP_PRODUCT_NAME
        INTO IV_SP_PRODUCT_NAME
        FROM TD_B_PARTY_PRODUCT A
       WHERE A.SP_ID = IV_SP_ID
         AND A.SP_PRODUCT_ID = IV_SP_PRODUCT_ID;
    
      SELECT DECODE(DATA_TYPE,
                    'NUMBER',
                    to_char(FEE / 100, 'FM9999999999990.90') || '元/月',
                    fee || '元/月') PRICE
        INTO IV_PRICE
        FROM (SELECT a.productcode,
                     a.productname,
                     nvl2(translate(SUBSTR(a.paravalue,
                                           5,
                                           INSTR(A.PARAVALUE, ';') - 5),
                                    '\1234567890',
                                    '\'),
                          'CHAR',
                          'NUMBER') DATA_TYPE,
                     CASE
                       WHEN nvl2(translate(SUBSTR(a.paravalue,
                                                  5,
                                                  INSTR(A.PARAVALUE, ';') - 5),
                                           '\1234567890',
                                           '\'),
                                 'CHAR',
                                 'NUMBER') = 'CHAR' THEN
                        SUBSTR(SUBSTR(a.paravalue,
                                      5,
                                      INSTR(A.PARAVALUE, ';') - 5),
                               1,
                               REGEXP_INSTR(SUBSTR(a.paravalue,
                                                   5,
                                                   INSTR(A.PARAVALUE, ';') - 5),
                                            '[^0-9]') - 1)
                       ELSE
                        TO_CHAR(NVL(SUBSTR(a.paravalue,
                                           5,
                                           INSTR(A.PARAVALUE, ';') - 5),
                                    0))
                     END FEE
                FROM td_b_party_price a
               WHERE a.productcode = IV_SP_PRODUCT_ID
                 AND a.billingmodecode = 'OrderMonth') T;
    
      IF IV_COUNT = 0 THEN
        IV_USER_SP_ALL := IV_SP_PRODUCT_NAME || '业务、资费' || IV_PRICE || '；';
      ELSE
        IV_USER_SP_ALL := IV_USER_SP_ALL || ' ' || IV_SP_PRODUCT_NAME ||
                          '业务、资费' || IV_PRICE || '；';
      END IF;
    
      IV_COUNT     := IV_COUNT + 1;
      IV_USE_COUNT := IV_USE_COUNT + 1;
      --IF IV_USE_Count = IV_CU_COUNT THEN
      --IV_SERIAL_NUMBER_TEMP := '0';
      --ELSE 
      IV_SERIAL_NUMBER_TEMP := IV_SERIAL_NUMBER;
      --END IF;
      --IV_SERIAL_NUMBER_TEMP := IV_SERIAL_NUMBER;
      IV_USER_ID_TEMP := IV_USER_ID;
    
    END LOOP;
    CLOSE IV_CURSOR;
    --DELETE ucr_crm1.fx_TEMP_TF_F_USER_SP_20130420;  临时表，无需删除
    COMMIT;
  
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      V_RESULTINFO := '定制生效的套餐发短信提醒报错:' || SQLERRM;
      V_RESULTCODE := -1;
  END;
  V_RESULTCODE := 0;
  V_RESULTINFO := 'trade OK！';
end P_SMS_SENDINFOTOUSER;

Answer 7

以上代码，还有很多可以优化的位置，可以加我ＱＱ：61691663　验证：csdn　远程帮你看看。