oracle 存储过程 性能调优

Question

工作是数据抽取，分主表和从表，是这样实现的，先从主表查询，然后游标循环
根据主表 主键，再去查从表，然后插入 主表，从表。
主表有1百多W数据量。
代码如下：create or replace procedure proc_test   as
 cursor c is 
 select t1.*
 from PERSON_INFO@viewOld t1   ;
 p PERSON_INFO@viewOld%ROWTYPE;
 
 pid number; 
 tezheng PERSON_BODYSURCHARACT@viewOld%ROWTYPE;
 rhyz PERSON_NORESIDENCE_HOLDER@viewOld%ROWTYPE;
   rhyz_isdelete   char(1);--  
  zk_sc   char(2);-- 
   zk_flag   char(1);-- 
   sc_flag   char(1);--   
   zk_sc_num number;
   
   tezheng_num number;
   rhyz_num number;
begin 
     FOR p IN c LOOP
      select SEQ_TB_RY.Nextval into pid from dual; 
     
      /* 
     --不知道是这样快 还是下面select count（*）块，我测试好像差不多
      begin
       select * into tezheng  from PERSON_BODYSURCHARACT@viewOld where V_PERSON_ID =p.v_person_id and rownum<2 ; --
        exception   
         when no_data_found then
         null;
        end;         */
     
     select count(V_PERSON_ID) into tezheng_num  from PERSON_BODYSURCHARACT@viewOld where V_PERSON_ID =p.v_person_id ;
       if tezheng_num >0 then
       select * into tezheng  from PERSON_BODYSURCHARACT@viewOld where V_PERSON_ID =p.v_person_id and rownum<2 ; --
        end if;
        
       select count(V_PERSON_ID) into rhyz_num from PERSON_NORESIDENCE_HOLDER@viewOld where V_PERSON_ID =p.v_person_id;
       if rhyz_num >0 then 
      select * into rhyz from PERSON_NORESIDENCE_HOLDER@viewOld where V_PERSON_ID =p.v_person_id and rownum<2 ; --
       end if;
        zk_sc:='0';
       select count(l.v_person_no) into zk_sc_num from  person_workobject3@viewOld c,PERSON_WORKOBJECT_LG@viewOld l  
       where p.v_person_id=c.v_person_id and c.v_person_no=l.v_person_no  and l.C_CADMIN_REASON is null;
       if zk_sc_num >0 then
       select  nvl(l.C_LADMIN_ZK,'0') into zk_sc from  person_workobject3@viewOld c,PERSON_WORKOBJECT_LG@viewOld l 
       where p.v_person_id=c.v_person_id and c.v_person_no=l.v_person_no and l.C_CADMIN_REASON is null and rownum<2 ;--
       end if;
      
       
   
       
       
      --
         rhyz_isdelete:=nvl( rhyz.C_UP,'0');
         if rhyz_isdelete='1' then rhyz_isdelete:='0';
         end if;
         if rhyz_isdelete='0' then rhyz_isdelete:='1';
         end if;
         zk_flag:='0';sc_flag:='0';
         if zk_sc='7' then zk_flag:='1';end if;
          if zk_sc='71' then sc_flag:='1';end if;
       
      --dbms_output.put_line(pid);
     
       insert into tb_ry (pid,SFZHM,XM,XB,CSRQ,JG,ZY,WHCD,MZ,HYZK,BMCH,ZJXY,ZZMM,LXSJ,GZBM,LXFS,FLAG_JSBDS,FLAG_SHGJZMRS,JKZK,ZZZHM,HJPCS,HJSX,
       HJSQ,HJJX,HJXZ,XZPCS,XZSX,XZSQ,XZJX,XZDZ,ZW,FLAG_JWZX,FLAG_LJSWZX,FLAG_JSJZ,FLAG_QBHS,FLAG_GZRY,SFAY,FLAG_FZCSW,FLAG_XFSF,FLAG_DSCC,FLAG_ZDRK,
       --
       LX,SG,ZCXH,TX,KY,TSTZ1,TSTZ2,TSTZ3,TSTZ4,TSTZ5,
       --
       DDBDSJ,LKBDSJ,JZBDYY,LKBDYY,FWXZ,YWWFFZQK,RHYZ,RHYZSJ,FLLX,RYQYLX,ZX,FWCS
       
       ) 
       values (pid,p.V_CARD_ID,p.V_NAME,p.C_SEX,p.d_birthdate,p.C_NATIVE,p.V_EMPLOYMENT,p.C_CULTURE_DEGR,p.C_NATION,p.C_MARRIAGE_STAT,
       p.V_NICKNAME || p.V_EVERNAME,p.C_RELIGION,p.C_POLITY_ISA,p.V_MOBILE,p.V_WORKUNIT,p.V_CONTACT_MODE,p.C_LUNACY,p.C_KNOW_NAME,
       p.C_HEALTH,p.V_RESIDENCE_NO,p.C_POLICE_STATION,p.V_CITY,p.C_COMMITTEE,p.C_STREET,p.V_DETAIL_ADDRESS,p.C_POLICESTATION_CUR,p.V_CITY_CUR
       ,p.C_COMMITTEE_CUR,p.C_STREET_CUR,p.V_DETAIL_ADDRESS_CUR,p.N_WORK_POS,p.C_CALLMON_FLAG,p.C_STAYMON_FLAG,p.C_MONIOTR_FLAG,p.c_Waitadmin_Flag
       ,p.C_ARREST_FLAG,p.C_TRAN_MARK,p.C_ABNORMAL_DIE,p.C_VISIT_MARK,sc_flag,zk_flag
       --
       ,tezheng.V_FACE_SHAPE,tezheng.N_STATURE,tezheng.N_SHOES_CODE,tezheng.V_BODY_SHAPE,tezheng.V_ACCENT,tezheng.V_CHARA1,tezheng.V_CHARA2,
       tezheng.V_CHARA3,tezheng.V_CHARA4,tezheng.V_CHARA5
       --
       ,rhyz.D_COME_DATE,rhyz.D_LEAVE_DATE,rhyz.V_LEAVE_REASON,rhyz.V_COME_REASON,rhyz.V_HOUSE_TYPE,rhyz.N_IS_CRIMED,rhyz.V_GATHER_PERSON,
       rhyz.D_RECORD_TIME,rhyz.V_APART_TYPE,rhyz.V_AREA_TYPE,rhyz_isdelete,rhyz.V_SERVICE_AREA
       
       );
      
      
     
      --从表1
      declare cursor help is 
     select *
      from PERSON_HELP@viewOld where V_PERSON_ID =p.v_person_id ;
       h PERSON_HELP@viewOld%ROWTYPE;
      begin 
      FOR h IN help LOOP  
      insert into TB_RY_BJ (PID,ID,SFZH,XM,XB,DW,ZZ,LXDH,ZBH) 
      values(SEQ_TB_RY_BJ.NEXTVAL,pid,nvl(h.V_IMPORTANT_CARDNO,''),nvl(h.V_HELP_NAME,''),nvl(h.C_HELP_SEX,''),nvl(h.V_HELP_UNIT,'')
      ,nvl(h.V_ADDRESS,''),nvl(h.V_HELP_PHONE,'') ,0); 
      commit; 
      end loop;  
          end;
          
        --从表2，3，4，5，6，7
一次类推。。都是这样的形式。
   
      commit; 
     end loop; 
end;

Answer 1

select count(*) into XXX from ... where rownum=1;
这样个人感觉比较适中

Answer 2

      insert into tb_ry (pid,SFZHM,XM,XB,CSRQ,JG,ZY,WHCD,MZ,HYZK,BMCH,ZJXY,ZZMM,LXSJ,GZBM,LXFS,FLAG_JSBDS,FLAG_SHGJZMRS,JKZK,ZZZHM,HJPCS,HJSX, 
      HJSQ,HJJX,HJXZ,XZPCS,XZSX,XZSQ,XZJX,XZDZ,ZW,FLAG_JWZX,FLAG_LJSWZX,FLAG_JSJZ,FLAG_QBHS,FLAG_GZRY,SFAY,FLAG_FZCSW,FLAG_XFSF,FLAG_DSCC,FLAG_ZDRK, 
      -- 
      LX,SG,ZCXH,TX,KY,TSTZ1,TSTZ2,TSTZ3,TSTZ4,TSTZ5, 
      -- 
      DDBDSJ,LKBDSJ,JZBDYY,LKBDYY,FWXZ,YWWFFZQK,RHYZ,RHYZSJ,FLLX,RYQYLX,ZX,FWCS 
      
      ) 
      values (pid,p.V_CARD_ID,p.V_NAME,p.C_SEX,p.d_birthdate,p.C_NATIVE,p.V_EMPLOYMENT,p.C_CULTURE_DEGR,p.C_NATION,p.C_MARRIAGE_STAT, 
      p.V_NICKNAME || p.V_EVERNAME,p.C_RELIGION,p.C_POLITY_ISA,p.V_MOBILE,p.V_WORKUNIT,p.V_CONTACT_MODE,p.C_LUNACY,p.C_KNOW_NAME, 
      p.C_HEALTH,p.V_RESIDENCE_NO,p.C_POLICE_STATION,p.V_CITY,p.C_COMMITTEE,p.C_STREET,p.V_DETAIL_ADDRESS,p.C_POLICESTATION_CUR,p.V_CITY_CUR 
      ,p.C_COMMITTEE_CUR,p.C_STREET_CUR,p.V_DETAIL_ADDRESS_CUR,p.N_WORK_POS,p.C_CALLMON_FLAG,p.C_STAYMON_FLAG,p.C_MONIOTR_FLAG,p.c_Waitadmin_Flag 
      ,p.C_ARREST_FLAG,p.C_TRAN_MARK,p.C_ABNORMAL_DIE,p.C_VISIT_MARK,sc_flag,zk_flag 
      -- 
      ,tezheng.V_FACE_SHAPE,tezheng.N_STATURE,tezheng.N_SHOES_CODE,tezheng.V_BODY_SHAPE,tezheng.V_ACCENT,tezheng.V_CHARA1,tezheng.V_CHARA2, 
      tezheng.V_CHARA3,tezheng.V_CHARA4,tezheng.V_CHARA5 
      -- 
      ,rhyz.D_COME_DATE,rhyz.D_LEAVE_DATE,rhyz.V_LEAVE_REASON,rhyz.V_COME_REASON,rhyz.V_HOUSE_TYPE,rhyz.N_IS_CRIMED,rhyz.V_GATHER_PERSON, 
      rhyz.D_RECORD_TIME,rhyz.V_APART_TYPE,rhyz.V_AREA_TYPE,rhyz_isdelete,rhyz.V_SERVICE_AREA 
      
      ); 上面的插入命令，可以改善。构造数组，每100笔或者更多，插入一次，可以改善IO.

Answer 3

最好是使用dbms_profiler 来跟踪你的存储过程,以及配合10046 level 8.关于dbms_profiler,可以参考我写过的日志.http://space.itpub.net/104152/viewspace-140025

Answer 4

通过批量处理会改善处理的速度，例如FORALL 也需要构造内存表。

Answer 5

  DECLARE  
        TYPE   DeptRecTab   IS   TABLE   OF   dept%ROWTYPE index   by   binary_integer;  
        dept_recs   DeptRecTab;  
        CURSOR   c1   IS  
              SELECT   deptno,   dname,   loc   FROM   dept   WHERE   deptno   >   10;  
  BEGIN  
        OPEN   c1;  
        FETCH   c1   BULK   COLLECT   INTO   dept_recs;  
  END;   上面是一个游标直接写入表变量的例子，也可是单独操作属性变量。
例如：
DEPT_RECS[1].DEPT_NO :='A001';
DEPT_RECS[2].DNAME :='HR';

Answer 6

你好，
构造内存表变量后，采用FORALL批量绑定的方式，一次性插入数据表。可以改善性能。
下面是一个完整的对比例子。希望能够有参考的意义。
SQL> CREATE OR REPLACE PROCEDURE bulktest
  2  IS
  3     TYPE numtab IS TABLE OF NUMBER (20)
  4        INDEX BY BINARY_INTEGER;
  5  
  6     TYPE nametab IS TABLE OF VARCHAR2 (50)
  7        INDEX BY BINARY_INTEGER;
  8  
  9     pnums    numtab;
 10     pnames   nametab;
 11     t1       NUMBER;
 12     t2       NUMBER;
 13     t3       NUMBER;
 14  BEGIN
 15     FOR j IN 1 .. 1000000
 16     LOOP
 17        pnums (j)         := j;
 18        pnames (j)        := 'Seq No. ' || TO_CHAR (j);
 19     END LOOP;
 20  
 21     SELECT DBMS_UTILITY.get_time
 22       INTO t1
 23       FROM DUAL;
 24  
 25     FOR i IN 1 .. 1000000
 26     LOOP
 27        INSERT INTO blktest
 28             VALUES (pnums (i), pnames (i));
 29     END LOOP;
 30  
 31     SELECT DBMS_UTILITY.get_time
 32       INTO t2
 33       FROM DUAL;
 34  
 35     FORALL i IN 1 .. 1000000
 36        INSERT INTO blktest
 37             VALUES (pnums (i), pnames (i));
 38  
 39     SELECT DBMS_UTILITY.get_time
 40       INTO t3
 41       FROM DUAL;
 42  
 43     DBMS_OUTPUT.put_line ('Execution Time (hsecs)');
 44     DBMS_OUTPUT.put_line ('---------------------');
 45     DBMS_OUTPUT.put_line ('FOR loop: ' || TO_CHAR (t2 - t1));
 46     DBMS_OUTPUT.put_line ('FORALL:   ' || TO_CHAR (t3 - t2));
 47  END;
 48  /Procedure created.

Answer 7

也有可能不是慢在insert这里啊，
所以让你使用dbms_profiler来辅助tuning呀．

Answer 8

我最近测了一个很简单的存储过程的性能，就是从一个表中根据参数取一条记录。但是感觉还没有直接执行sql语句来的快，不知道是什么原因。

Answer 9

存储过程优化法则：
   1.禁用游标，减少sql引擎和PL/sql引擎数据交换
   2.oracle 的sql是集合操作语言，进行批操作，但这个批要合适，一般建议为50万到500万，取决于你的整体数据规模
   3.for all不是循环，归根揭底还是模拟循环操作，只是一次提交给sql引擎一批sql而已，还是行操作,所以性能肯定不是最优的，但比游标循环要好的多