存儲過程跑了很久

Question

procedure JUN_MIS_ADV_SIZE_0917_2 is       V_SUM char(2);
    BEGIN           DECLARE CURSOR C1 IS 
       (SELECT * FROM JUN_23_0916 );
      BEGIN  
      
            FOR ITEM1 IN C1 LOOP
              BEGIN
                   SELECT NVL2(MAX(TO_NUMBER(JOINT_NO)),MAX(TO_NUMBER(JOINT_NO))+1,0) INTO V_SUM 
                   FROM JUN_MIS_ADV_SIZE_0917_2  
                   WHERE ARTIC_NO_IN = ITEM1.ARTIC_NO_IN AND MAT_NO = ITEM1.MAT_NO
                     AND PART_NO = ITEM1.PART_NO;                   
                      INSERT INTO JUN_MIS_ADV_SIZE_0917_2(
                                                              FACT_NO,
                                                             ARTIC_NO_IN,
                                                             ARTIC_NO,
                                                             PART_NO,
                                                             JOINT_NO,
                                                             SIZERUN,
                                                             MOLD_SIZE,
                                                             MAT_NO) 
                              (
                              SELECT ITEM1.FACT_NO,ITEM1.ARTIC_NO_IN,ITEM1.ARTIC_NO,
                                           ITEM1.PART_NO, 
                                           TO_NUMBER(V_SUM),
                                           TO_NUMBER(ITEM1.SIZERUN),ITEM1.MOLD_SIZE,ITEM1.MAT_NO
                              FROM DUAL
                              );                           
              END ;         
       END LOOP
       commit;    
     END;end JUN_MIS_ADV_SIZE_0917_2;
為什么這個存儲過程需要跑 12062.516秒啊，就十幾萬條數據啊！

Answer 1

楼主用EXPLAIN PLAN 单独分析下这2个SQL语句，看下效率怎样,能否优化
SELECT NVL2(MAX(TO_NUMBER(JOINT_NO)),MAX(TO_NUMBER(JOINT_NO))+1,0) INTO V_SUM 
                  FROM JUN_MIS_ADV_SIZE_0917_2  
                  WHERE ARTIC_NO_IN = ITEM1.ARTIC_NO_IN AND MAT_NO = ITEM1.MAT_NO 
                    AND PART_NO = ITEM1.PART_NO;                  
                      INSERT INTO JUN_MIS_ADV_SIZE_0917_2( 
                                                              FACT_NO, 
                                                            ARTIC_NO_IN, 
                                                            ARTIC_NO, 
                                                            PART_NO, 
                                                            JOINT_NO, 
                                                            SIZERUN, 
                                                            MOLD_SIZE, 
                                                            MAT_NO) 
                              ( 
                              SELECT ITEM1.FACT_NO,ITEM1.ARTIC_NO_IN,ITEM1.ARTIC_NO, 
                                          ITEM1.PART_NO, 
                                          TO_NUMBER(V_SUM), 
                                          TO_NUMBER(ITEM1.SIZERUN),ITEM1.MOLD_SIZE,ITEM1.MAT_NO 
                              FROM DUAL 
                              );                          还有插入数据时使用nologging模式，这样可能要快点...1. sql> alter   table   table_name   NOLOGGING; 
2. INSERT   /*+Append*/   INTO     tab1  SELECT   *   FROM   tab2;
3. sql> alter   table   table_name   LOGGING; 

Answer 2

不用游标
CREATE OR REPLACE PROCEDURE JUN_MIS_ADV_SIZE_0917_2 
AS
BEGIN
INSERT INTO JUN_MIS_ADV_SIZE_0917_2(FACT_NO,ARTIC_NO_IN,ARTIC_NO,PART_NO,JOINT_NO,SIZERUN,MOLD_SIZE,MAT_NO)
SELECT b.FACT_NO,b.ARTIC_NO_IN,b.ARTIC_NO,b.PART_NO,TO_NUMBER(NVL2(MAX(TO_NUMBER(JOINT_NO)),MAX(TO_NUMBER(JOINT_NO))+1,0)),TO_NUMBER(b.SIZERUN),b.MOLD_SIZE,b.MAT_NO 
FROM JUN_MIS_ADV_SIZE_0917_2 a,JUN_23_0916 b
WHERE ARTIC_NO_IN = b.ARTIC_NO_IN AND MAT_NO = b.MAT_NO AND PART_NO = b.PART_NO;
END JUN_MIS_ADV_SIZE_0917_2; 

Answer 3

1，执行存储过程的时候，把你主表的触发器全部关掉，当存储过程执行完毕之后，再打开所有触发器。
实例：
alter table tb disnable all triggers; 
alter table tb enable all triggers;2，将显示游标换成隐式游标
实例：
    for cur in (select y.seq_id,y.customer_tel_id,y.merger_flag from YH_ALL_FLAG_TEMP y where y.type=1 ) loop
        update t_customer_tel t 
        set t.merger_flag=cur.merger_flag,
            t.update_dt=sysdate
        where t.customer_tel_id=cur.customer_tel_id;
        if(mod(cur.seq_id,10000)=0 or cur.seq_id=v_max) then
              commit;                     
        end if;
    end loop;

Answer 4

10萬數據的table裡面用遊標好像速度也還好吧？