紧急请教高手 存储过程效率问题

Question

CREATE OR REPLACE PROCEDURE REPAIR_AGR_DATA IS
  PRO_FILE  UTL_FILE.FILE_TYPE;
  V_NUM        NUMBER;
  V_DJSJ       VARCHAR2(14);
  V_JDID       VARCHAR2(14);
  V_DQFB_ZS    VARCHAR2(20);
  V_NLD_ZS     VARCHAR2(20);
  V_NLD_NLDRS3 VARCHAR2(20);
  V_NLD_NLDRS5 VARCHAR2(20);
  TIME_BEGIN  DATE;
  CURSOR CS_NLD_SL IS
    SELECT T.NLD_DJSJ, T.NLD_JDID, T.NLD_LDRKZS
      FROM TJFX_NLDTJ_TEMP_BAK T
     WHERE T.NLD_DJSJ > '20080000000000'
       AND SUBSTR(T.NLD_JDID, 10, 3) <> '000'
       AND T.NLD_DJSJ IN
           (SELECT NLD_DJSJ FROM TJFX_NLDTJ_TEMP_BAK GROUP BY NLD_DJSJ)
     ORDER BY 2, 1;
BEGIN
  TIME_BEGIN:=sysdate;
  V_NUM := 0;
  PRO_FILE:=UTL_FILE.FOPEN('UTL_FILE_DIR','存储过程进度.txt','A');
  OPEN CS_NLD_SL;
  LOOP
    FETCH CS_NLD_SL
      INTO V_DJSJ, V_JDID, V_NLD_ZS;
    EXIT WHEN CS_NLD_SL%NOTFOUND;
    V_NUM := V_NUM + 1;
    SELECT Z.RFD_LDRYZS
      INTO V_DQFB_ZS
      FROM SJCJ_RFDTJ_TEMP_LSXX_BAK Z
     WHERE Z.RFD_JDID = V_JDID
       AND SUBSTR(Z.RFD_DJSJ, 1, 8) = SUBSTR(V_DJSJ, 1, 8);
    V_NLD_NLDRS3 := FLOOR((V_DQFB_ZS - V_NLD_ZS) *
                          ROUND(DBMS_RANDOM.VALUE(0.58, 0.65), 2));
    V_NLD_NLDRS5 := (V_DQFB_ZS - V_NLD_ZS) - V_NLD_NLDRS3;
    UPDATE TJFX_NLDTJ_TEMP_BAK
       SET NLD_NLDRS3 = V_NLD_NLDRS3, NLD_NLDRS5 = V_NLD_NLDRS5
     WHERE NLD_DJSJ = V_JDID
       AND NLD_JDID = V_JDID;
    IF MOD(V_NUM, 15000) = 0 THEN
      COMMIT;
      UTL_FILE.PUT_LINE(PRO_FILE,'-------已修改 '||V_NUM||' 条--------------');
      UTL_FILE.FFLUSH(PRO_FILE);
   UTL_FILE.PUT_LINE(PRO_FILE,'-------已耗时 '||TO_CHAR((SYSDATE-TIME_BEGIN)*24*60)||'分钟--------------');
   UTL_FILE.FFLUSH(PRO_FILE);
    END IF;
  END LOOP;
  COMMIT;
  --------处理街乡级年龄段数据-------------
  UPDATE TJFX_NLDTJ_TEMP_BAK J
     SET (J.NLD_NLDRS3, J.NLD_NLDRS5) = (SELECT SUM(T.NLD_NLDRS3),
                                                SUM(T.NLD_NLDRS5)
                                           FROM TJFX_NLDTJ_TEMP_BAK T
                                          WHERE T.NLD_DJSJ = J.NLD_DJSJ
                                            AND SUBSTR(T.NLD_JDID, 1, 9) =
                                                SUBSTR(J.NLD_JDID, 1, 9)
                                            AND T.NLD_JDID <> J.NLD_JDID)
   WHERE SUBSTR(J.NLD_JDID, 10, 3) = '000'
     AND SUBSTR(J.NLD_JDID, 7, 3) <> '000';
  COMMIT;
UTL_FILE.PUT_LINE(PRO_FILE,'------- 已处理完街乡级数据--------------');
      UTL_FILE.FFLUSH(PRO_FILE);
    UTL_FILE.PUT_LINE(PRO_FILE,'-------已耗时 '||TO_CHAR((SYSDATE-TIME_BEGIN)*24*60)||'分钟--------------');
   UTL_FILE.FFLUSH(PRO_FILE);
  ------------处理区县年龄段数据------------
  UPDATE TJFX_NLDTJ_TEMP_BAK J
     SET (J.NLD_NLDRS3, J.NLD_NLDRS5) = (SELECT SUM(T.NLD_NLDRS3),
                                                SUM(T.NLD_NLDRS5)
                                           FROM TJFX_NLDTJ_TEMP_BAK T
                                          WHERE T.NLD_DJSJ = J.NLD_DJSJ
                                            AND SUBSTR(T.NLD_JDID, 1, 6) =
                                                SUBSTR(J.NLD_JDID, 1, 6)
                                            AND T.NLD_JDID <> J.NLD_JDID
                                            AND SUBSTR(T.NLD_JDID, 10, 3) <>
                                                '000')
   WHERE SUBSTR(J.NLD_JDID, 7, 3) = '000'
     AND SUBSTR(J.NLD_JDID, 4, 3) <> '000';
  COMMIT;
UTL_FILE.PUT_LINE(PRO_FILE,'------- 已处理完区县级数据--------------');
      UTL_FILE.FFLUSH(PRO_FILE);
     UTL_FILE.PUT_LINE(PRO_FILE,'-------已耗时 '||TO_CHAR((SYSDATE-TIME_BEGIN)*24*60)||'分钟--------------');
   UTL_FILE.FFLUSH(PRO_FILE);
  -----------处理市级年龄段数据------
  UPDATE TJFX_NLDTJ_TEMP_BAK J
     SET (J.NLD_NLDRS3, J.NLD_NLDRS5) = (SELECT SUM(T.NLD_NLDRS3),
                                                SUM(T.NLD_NLDRS5)
                                           FROM TJFX_NLDTJ_TEMP_BAK T
                                          WHERE T.NLD_DJSJ = J.NLD_DJSJ
                                            AND T.NLD_JDID LIKE
                                                '110___000000'
                                            AND T.NLD_JDID <> J.NLD_JDID
                                            AND SUBSTR(J.NLD_JDID, 7, 3) <>
                                                '000'
                                            AND SUBSTR(T.NLD_JDID, 10, 3) <>
                                                '000')
   WHERE NLD_JDID = '110000000000';
  COMMIT;
UTL_FILE.PUT_LINE(PRO_FILE,'------- 已处理完市级数据--------------');
      UTL_FILE.FFLUSH(PRO_FILE);
    UTL_FILE.PUT_LINE(PRO_FILE,'-------已耗时 '||TO_CHAR((SYSDATE-TIME_BEGIN)*24*60)||'分钟--------------');
   UTL_FILE.FFLUSH(PRO_FILE);
exception 
when others then
 UTL_FILE.PUT_LINE(PRO_FILE,'--------出错的SQL语句为：'||SQLCODE);
 UTL_FILE.PUT_LINE(PRO_FILE,'--------出错信息：'||SQLERRM);
      UTL_FILE.FFLUSH(PRO_FILE);
END;如上所示，游标含的数据大概是80万，我是按1万5提交一次，为什么每提交一次后，
 UPDATE TJFX_NLDTJ_TEMP_BAK
       SET NLD_NLDRS3 = V_NLD_NLDRS3, NLD_NLDRS5 = V_NLD_NLDRS5
     WHERE NLD_DJSJ = V_JDID
       AND NLD_JDID = V_JDID;
更新速度就会慢上一倍，诚信请教大侠，如何改进以及原因，谢谢了

Answer 1

我们一般是在批次中采用每500笔提交一次 ，我感觉你1万五才提交有点太大了，不然会照成回滚段数据较多，redo log 空间得不到及时释放
但是这样处理要看你是否对异常发生后怎么处理，是全部回滚还是部分回滚

Answer 2

4.update tablename nologging 加上nologging选项。
5.加大日志文件的大小也可以。

Answer 3

仁兄的ID'YY-MM-DD'有意思 
你说的禁用表上的索引和约束指的是删掉索引和约束吗？
”使用并行处理方式“ 指的是？

Answer 4

论坛上老看到你的身影啊 敢问paddy为什么后面执行的速度会变慢 是redo log使用空间增大造成的吗？

Answer 5

看了一下你具体的过程，是对同一张表查询出数据在根据条件对该表进行更新
试试采用游标更新的方法 效果如何
还有更新时，最好禁止你的index，过程最后再rebuild
.....
.....
CURSOR CS_NLD_SL IS
    SELECT T.NLD_DJSJ, T.NLD_JDID, T.NLD_LDRKZS
    FROM TJFX_NLDTJ_TEMP_BAK T
    WHERE .....
    FOR UPDATE ; --用游标实现更新
......
......  UPDATE TJFX_NLDTJ_TEMP_BAK
       SET NLD_NLDRS3 = V_NLD_NLDRS3, NLD_NLDRS5 = V_NLD_NLDRS5
  WHERE  CURRENT  OF CS_NLD_SL;  ......
.....

Answer 6

再问大家一下，
TJFX_NLDTJ_TEMP_BAK 表大概是88万的数据，
进行这样的修改（修改数据量为80万）
UPDATE TJFX_NLDTJ_TEMP_BAK
  SET NLD_NLDRS3 = V_NLD_NLDRS3, NLD_NLDRS5 = V_NLD_NLDRS5
  WHERE NLD_DJSJ = V_JDID
  AND NLD_JDID = V_JDID; 最快大约为多长时间
我马上按各位的建议再重测一遍

Answer 7

就是把你表上约束disable掉，不进行删除

Answer 8

你后面更新慢是用从时间来看的吧：TO_CHAR((SYSDATE-TIME_BEGIN)*24*60)
你每更新15000笔，所用的时间是从开始到现在的时间，而不是每15000笔执行的时间
因为你这里 TIME_BEGIN是定了的
也就是说你后面没更新15000其实是加了前面的时间，所以你看起来执行时间较大
不知道我理解对你说的没有？

Answer 9

  CURSOR CS_NLD_SL IS
    SELECT T.NLD_DJSJ, T.NLD_JDID, T.NLD_LDRKZS
      FROM TJFX_NLDTJ_TEMP_BAK T
     WHERE T.NLD_DJSJ > '20080000000000'
       AND SUBSTR(T.NLD_JDID, 10, 3) <> '000'
       AND T.NLD_DJSJ IN
           (SELECT NLD_DJSJ FROM TJFX_NLDTJ_TEMP_BAK GROUP BY NLD_DJSJ)
     ORDER BY 2, 1;这个游标写的有问题，可以像这样优化下
  CURSOR CS_NLD_SL IS
    SELECT T.NLD_DJSJ, T.NLD_JDID, T.NLD_LDRKZS
      FROM TJFX_NLDTJ_TEMP_BAK T
     WHERE T.NLD_DJSJ IN
           (SELECT NLD_DJSJ FROM TJFX_NLDTJ_TEMP_BAK GROUP BY NLD_DJSJ)
       AND SUBSTR(T.NLD_JDID, 10, 3) <> '000'
       AND  T.NLD_DJSJ > '20080000000000'
     ORDER BY 2, 1;
另外尽量用索引，尽量不用substr。别的地方你都看下，所有用substr的地方都会很慢。而你用了很多substr，想办法优化下吧。

Answer 10

大批量的插入数据 已经快占满redo 空间 ，又没有释放回滚段 所以你在更新肯定很慢
把插入的表 以及更新的表 alter table tbname nologging

Answer 11

感谢各位的回复，我禁掉了表上的索引 也修改了table nologgin属性
按1000次提交，速度大概是1小时更新15万条，怎么觉得还是很慢，请问
大侠们还有什么高招

Answer 12

补充说明下 存储过程就是对一张表TJFX_NLDTJ_TEMP_BAK 更新，表总共大概80万的数据，需要更新的大概有70多万，怎么才能让他更新的更快