写了个过程往表里插数据,用游标循环1s才插1条,测试400数据就跑了6分多钟,应用150W+数据得跑死机了,求告知该如何优化,小白献上膝盖

解决方案 »

  1.   


    create or replace procedure L_P_ONU_VER isT_SQL        VARCHAR2(4000);
    CUR1      PKG_TYPE_DEFINE.TYPE_REF_CURSOR;
    TYPE V_ONU_VIEW IS TABLE OF ONU_VER_VIEW%ROWTYPE;
    T_ONU_VIEW V_ONU_VIEW;
    T_ONU_VER  ONU_VER_TEST%ROWTYPE;
    BEGIN 
     
      T_SQL :='SELECT * FROM ONU_VER_VIEW WHERE 1=1';  --给游标用到的数据
      
      OPEN CUR1 FOR T_SQL;
      LOOP
        FETCH CUR1 BULK COLLECT 
         INTO T_ONU_VIEW LIMIT 1000;
        EXIT WHEN T_ONU_VIEW.COUNT=0;
        FOR I IN 1 .. T_ONU_VIEW.COUNT LOOP
          BEGIN 
            T_ONU_VER:=NULL;
            
            T_ONU_VER.VER_ID     := T_ONU_VIEW(I).VER_ID;
            T_ONU_VER.VENDOR     := T_ONU_VIEW(I).VENDOR;
            T_ONU_VER.MODEL      := T_ONU_VIEW(I).MODEL;
            T_ONU_VER.IP         := T_ONU_VIEW(I).IP;
            T_ONU_VER.AREA_ID    := T_ONU_VIEW(I).AREA_ID;
            T_ONU_VER.CON_TIME   := T_ONU_VIEW(I).CON_TIME;
            T_ONU_VER.REG_STATUS := T_ONU_VIEW(I).REG_STATUS;
            T_ONU_VER.LOGIC_ID   := T_ONU_VIEW(I).LOGIC_ID;
            T_ONU_VER.HD_VERSION := T_ONU_VIEW(I).HD_VERSION;
            T_ONU_VER.SW_VERSION := T_ONU_VIEW(I).SW_VERSION;
            
            -- T_ONU_VER.SN
             BEGIN 
                SELECT O.LOGIC_ID, 
                       CASE WHEN A.SN=T_ONU_VER.LOGIC_ID THEN 'Y' ELSE 'N' END SN
                  INTO T_ONU_VER.LOGIC_ID,
                       T_ONU_VER.SN
                  FROM ONU_VER_VIEW O
                  LEFT JOIN SERVICE.INTF_DEVICE_ALL A ON A.SN=O.LOGIC_ID
                  WHERE O.LOGIC_ID=T_ONU_VER.LOGIC_ID;    
             EXCEPTION
                  WHEN OTHERS THEN
                    NULL;
             END;
            -- T_ONU_VER.KD_ID,YY_ID,GQ_ID
              IF T_ONU_VER.SN='Y'THEN
                BEGIN
                  SELECT IO1.ACCESSNO KD_ID,
                         IO2.ACCESSNO YY_ID,
                         IO3.ACCESSNO GQ_ID
                    INTO T_ONU_VER.KD_ID,
                         T_ONU_VER.YY_ID,
                         T_ONU_VER.GQ_ID
                    FROM T_RES_DEVICE D
                    LEFT JOIN SERVICE.INTF_ORDERINFO IO1
                    ON D.DEV_NAME=IO1.ACCESSNO
                    AND IO1.PRODUCTID NOT IN ('1','3','13','3816','1004','500000165','500000166','90022')
                    LEFT JOIN SERVICE.INTF_ORDERINFO IO2 
                    ON D.DEV_NAME=IO2.ACCESSNO
                    AND IO2.PRODUCTID IN ('3816','1004','500000165','500000166','90022')
                    LEFT JOIN SERVICE.INTF_ORDERINFO IO3
                    ON D.DEV_NAME=IO3.ACCESSNO
                    AND IO3.PRODUCTID IN ('1','3','13','100109')
                    WHERE D.DEV_TYPE_ID IN (7,8)
                    AND D.IS_VALID='Y'
                    AND D.RESERVE20=T_ONU_VER.LOGIC_ID
                    AND ROWNUM<2;
                EXCEPTION
                  WHEN OTHERS THEN
                    NULL;
                END;
              END IF;
            --PON_LOID
                BEGIN
                  SELECT O.LOGIC_ID, 
                         CASE WHEN A.LOGIC_ID=T_ONU_VER.LOGIC_ID THEN 'Y' ELSE 'N' END PON_LOID
                    INTO T_ONU_VER.LOGIC_ID,
                         T_ONU_VER.PON_LOID
                    FROM ONU_VER_VIEW O
                    LEFT JOIN T_RES_SYNC_ONU A ON A.LOGIC_ID=O.LOGIC_ID
                    WHERE O.LOGIC_ID=T_ONU_VER.LOGIC_ID; 
                EXCEPTION
                  WHEN OTHERS THEN
                    NULL;
                END;          IF T_ONU_VER.PON_LOID='Y' THEN
            --OLT_IP,OLT_PON,ONU_ID,ONU_NAME
                  BEGIN
                    SELECT A.OLT_ID OLT_IP,
                           A.PON_ID OLT_PON,
                           A.ONU_NO ONU_ID,
                           A.ONU_NAME ONU_NAME
                      INTO T_ONU_VER.OLT_IP,
                           T_ONU_VER.OLT_PON,
                           T_ONU_VER.ONU_ID,
                           T_ONU_VER.ONU_NAME
                      FROM T_RES_SYNC_ONU A
                      WHERE A.LOGIC_ID=T_ONU_VER.LOGIC_ID;
                  EXCEPTION
                    WHEN OTHERS THEN
                      NULL;
                  END;   
            
            --OLT_NAME,ONU_STATUS
                  BEGIN
                    SELECT D.DEV_NAME,
                           A.IF_OPER_STATUS ONU_STATUS
                      INTO T_ONU_VER.OLT_NAME,
                           T_ONU_VER.ONU_STATUS
                      FROM T_RES_DEVICE D
                      LEFT JOIN T_RES_PORT A
                      ON A.DEV_ID=D.DEV_ID
                      AND A.IF_POSITION=T_ONU_VER.OLT_PON
                      WHERE D.DEV_TYPE_ID IN (6,9)
                      AND D.IS_VALID='Y'
                      AND D.IP_ADDR=T_ONU_VER.OLT_IP;
                  EXCEPTION
                    WHEN OTHERS THEN
                      NULL;
                  END;
              END IF;
              select  sysdate into t_ONu_ver.Last_Up_Time  from dual;
              INSERT INTO ONU_VER_TEST VALUES T_ONU_VER;
             
              COMMIT;         
               DBMS_OUTPUT.put_line(t_ONu_ver.Last_Up_Time);
          EXCEPTION
            WHEN OTHERS THEN
              NULL;
          END;
        END LOOP;
      END LOOP;
      CLOSE CUR1;END L_P_ONU_VER;
      

  2.   

    先用dbms_profiler包分析一下时间消耗在哪里吧.
    如果是用的plsql developer工具, 在test界面, 点击create profiler report, 然后执行过程;完成后查看profiler选项卡就行了
      

  3.   

    哥我有一个笨的 办法, 我不会用执行计划,你先把你的 其他的 查询的去掉 先弄一那个骨架执行下 看看是不是慢,如果慢看看具体的是那个sql慢 ,然后在 吧其他的条件加上,一点一点的看,如果是查询慢建一个索引试试,还有就是 你尽量批量处理 别插一条就提交一次,比如说你插了一千条一次性的提交。或者你解决了把方法告诉我下我也想提升