存储过程优化 请高手帮忙看看

Question

PROCEDURE ins_u_pcbx(p_pcab01 b_pcab.pcab01%type,p_dblink b_pcab.pcab03%type)
   is
   pcbx      u_pcbx%rowtype;
   g_success    VARCHAR2(1) ;
   g_success_b  VARCHAR2(1) ;
   v_errm    VARCHAR2(10);
   l_key     VARCHAR2(50);
   l_err_2     NUMBER(5);
   l_i_2       NUMBER(5);
   l_pcad02    b_pcad.pcad02%type;
   TYPE  CURSYJ IS REF CURSOR;
   pcbx_cursor CURSYJ;
   BEGIN
        ins_log_detail1(l_no,'u_pcbx',p_pcab01,l_pcad02);
        l_err_2 := 0;
        l_i_2 := 0;
        l_sql := ' TRUNCATE table POSGC_tmp';
        EXECUTE IMMEDIATE l_sql;
        l_sql := ' TRUNCATE table POSGD_tmp';
        EXECUTE IMMEDIATE l_sql;
        l_sql := ' INSERT INTO POSGC_TMP (SELECT * FROM "POSGC"@'||p_dblink||' WHERE "TRANS_FLG" =''N'' AND "TRANS_TYPE" = ''0'' AND "SHOP" =:a) ';
        EXECUTE IMMEDIATE l_sql USING p_pcab01;
        l_sql := ' INSERT INTO POSGD_TMP (SELECT * FROM "POSGD"@'||p_dblink||' WHERE  "TRANS_TYPE" = ''0'' AND "SHOP" =:a) ';
        EXECUTE IMMEDIATE l_sql USING p_pcab01;
        COMMIT;
        l_sql := 'SELECT '''','''','''','''','''','''','''','''','''',SHOP,FNO,SUCODE,SFNO,SHOP1,SHOP2, '||
                 ' FDATE,DLVMAN,MEMO,CNFMAN,CNFDATE,CNFTIME,CNFMAN2,CNFDATE2,CNFTIME2,CNFFLG'||
                 ' FROM POSGC_TMP';
         OPEN  pcbx_cursor FOR l_sql ;
        LOOP
         FETCH pcbx_cursor INTO pcbx;
         EXIT WHEN pcbx_cursor%notfound;
         g_success    := 'Y';
         g_success_b  := 'Y';
         BEGIN
           DELETE  FROM u_pcbx  WHERE pcbx00 = pcbx.pcbx00 AND pcbx01 = pcbx.pcbx01 ;
           INSERT INTO u_pcbx(TRANS_SHOP,TRANS_TYPE,TRANS_NO,PUSH_NO,PULL_NO,TRANS_STAT,
                              PROCS_CODE,PROCS_NO,PROCS_ITEM,PCBX00,PCBX01,PCBX02,PCBX03,
                              PCBX04,PCBX05,PCBX06,PCBX07,PCBX08,PCBX09,PCBX10,PCBX11,PCBX12,PCBX13,PCBX14,PCBXCONF)
                VALUES (p_pcab01,'0',l_no,'','','','N','','',p_pcab01,pcbx.pcbx01,
                        pcbx.pcbx02,pcbx.pcbx03,pcbx.pcbx04,pcbx.pcbx05,pcbx.pcbx06,
                        pcbx.pcbx07,pcbx.pcbx08,pcbx.pcbx09,pcbx.pcbx10,pcbx.pcbx11,
                        pcbx.pcbx12,pcbx.pcbx13,pcbx.pcbx14,pcbx.pcbxconf);
            EXCEPTION
             WHEN OTHERS THEN
               g_success := 'N';
               v_errm    := sqlcode;
               l_key     := p_pcab01||'_'|| pcbx.pcbx01;
               ins_log_detail2(l_no,v_errm,l_pcad02,l_key,'2');
            END;
            IF g_success ='Y' THEN
              ins_u_pcby(p_pcab01,p_dblink,pcbx.pcbx01,g_success_b,l_pcad02);
            BEGIN
             l_sql := 'UPDATE "POSGC"@'||p_dblink||' SET "TRANS_FLG" = ''Y'' ,"TRANS_NO" = :a WHERE "SHOP" = :b AND "FNO" = :c ';
             EXECUTE IMMEDIATE l_sql USING l_no,p_pcab01,pcbx.pcbx01;
             EXCEPTION
             WHEN OTHERS THEN
                g_success := 'N';
                 v_errm    := sqlcode;
                 l_key     := p_pcab01||'_'|| pcbx.pcbx01;
                 ins_log_detail2(l_no,v_errm,l_pcad02,l_key,'1');
               END;
            END IF;
            IF g_success = 'Y' THEN
               l_i_2:=l_i_2 +1 ;
               COMMIT;
            ELSE
               l_err_2 := l_err_2 +1 ;
               ROLLBACK;
            END IF;
        END LOOP;
        CLOSE pcbx_cursor;
        upd_log_detail1(l_no,l_pcad02,l_err_2,l_i_2);
   END;
PROCEDURE ins_u_pcby(p_pcab01 b_pcab.pcab01%type,p_dblink b_pcab.pcab03%type,
                        p_pcby01 u_pcby.pcby01%type,
                        p_success IN out VARCHAR2,
                        p_pcad02 b_pcad.pcad02%type)
   is
   pcby      u_pcby%rowtype;
   v_errm    VARCHAR2(10);
   l_key     VARCHAR2(50);
   TYPE  CURSYJ IS REF CURSOR;
   pcby_cursor CURSYJ;
   BEGIN
      l_sql := 'SELECT '''','''','''','''','''','''','''',SHOP,FNO,ITEM,PROD,CLRNO,SIZENO, '||
               ' UNIT,QTY,SHOP1,WNO1,SHOP2,WNO2,MEMO,CNFFLG'||
               ' FROM POSGD_TMP  WHERE  TRANS_TYPE = ''0'' AND SHOP =:a AND FNO = :b ';
         OPEN  pcby_cursor FOR l_sql USING p_pcab01,p_pcby01 ;
         LOOP
           FETCH pcby_cursor INTO pcby;
           EXIT WHEN pcby_cursor%notfound;
           BEGIN
           DELETE  FROM u_pcby  WHERE pcby00 = pcby.pcby00 AND pcby01 = pcby.pcby01 AND pcby02 = pcby.pcby02 ;
           INSERT INTO u_pcby(TRANS_SHOP,TRANS_TYPE,TRANS_NO,PULL_NO,PROCS_CODE,PROCS_NO,PROCS_ITEM,
                              PCBY00,PCBY01,PCBY02,PCBY03,PCBY04,PCBY05,PCBY06,PCBY07,PCBY08,PCBY09,
                              PCBY10,PCBY11,PCBY12,PCBYCONF)
                VALUES (p_pcab01,'0',l_no,'','N','','',p_pcab01,pcby.pcby01,pcby.pcby02,pcby.pcby03,
                        pcby.pcby04,pcby.pcby05,pcby.pcby06,pcby.pcby07,pcby.pcby08,
                        pcby.pcby09,pcby.pcby10,pcby.pcby11,pcby.pcby12,pcby.pcbyconf);
           EXCEPTION
             WHEN OTHERS THEN
               p_success := 'N';
               v_errm    := sqlcode;
               l_key     := p_pcab01||'_'|| pcby.pcby01||'_'|| pcby.pcby02;
               ins_log_detail2(l_no,v_errm,p_pcad02,l_key,'2');
            END;
             IF p_success ='Y' THEN
             BEGIN
             l_sql := 'UPDATE "POSGD"@'||p_dblink||' SET "TRANS_NO" = :a WHERE "SHOP" = :b AND "FNO" = :c '||
                      ' AND "ITEM" = :d  ';
             EXECUTE IMMEDIATE l_sql USING l_no,p_pcab01,pcby.pcby01,pcby.pcby02;
             EXCEPTION
             WHEN OTHERS THEN
                 p_success := 'N';
                 v_errm    := sqlcode;
                 l_key     := p_pcab01||'_'|| pcby.pcby01||'_'|| pcby.pcby02;
                 ins_log_detail2(l_no,v_errm,p_pcad02,l_key,'1');
              END;
            END IF;
        END LOOP;
        CLOSE pcby_cursor;
   END;上面2个PROCEDURE是存储过程处理某单据的部分 现在处理5W笔单据,需要35分钟 但是一天的单据量是100W 那样消耗的时间太久 我想请教下上面的部分有没有需要优化的?(是从sqlserver上读数据 然后写到oracle 通过getway dblink)

Answer 1

过程有点复杂，我给你提个建议。你可以试一下，
你在循环和调用游标的地方加上debug，如果不会，就新建一个表，调试的信息插到表里分析。那段慢，然后攻克他。
5w在60秒内搞定没问题。

Answer 2

INSERT INTO u_pcby
(TRANS_SHOP,TRANS_TYPE,TRANS_NO,PULL_NO,PROCS_CODE,PROCS_NO,PROCS_ITEM, 
                              PCBY00,PCBY01,PCBY02,PCBY03,PCBY04,PCBY05,PCBY06,PCBY07,PCBY08,PCBY09, 
                              PCBY10,PCBY11,PCBY12,PCBYCONF) 
                VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,
                        :12,:13,:14,:15,:16,:17,:18,:19,:20,:21); 
EXECUTE IMMEDIATE  l_sql USING l_no,p_pcab01,pcby.pcby01,pcby.pcby02,......
以此类推的赋值,这样应该能快很多.
你还可以考虑一下分布提交,大概每5000条左右COMMIT一次

Answer 3

LOOP循环了5000次以后执行一次COMMIT,这样也应该能够提高速度

Answer 4

楼主写得挺专业的,赞同分批提交,不用每一笔都提交一次,呵呵..
这种DB Link挺麻烦的,有时还会有一些奇怪的问题发生.