【Oracle】多次提交造成性能慢及处理方法

efdf

【问题背景】 2013-08-02 为某地市做了1个脚本用于帮客户账户添加一个新的账本。犯了一个很二的错，存储过程如下（SQL记录用户以后查询），一晚上只执行了190W数据 脚本如下 数据库中总共有500W或者更多的客户。

【问题原因】 但由于每次循环时都COMMIT提交，导致效率低，
      相比之下，每1W条提交，肯定是更有效率的
因为每一次commit会触发LGWR做很多相应的操作，将重做BUFFER中的数据写到日志当中，然后去处理回滚段的相应信息，让回滚段中900秒（默认）之外的信息可以被重写。
也就是每一次commit都会带来大量的操作。
但是，commit的数据，也并不是全部完成之后提交一次效率就高了，因为还要看data_buffer的大小。
commit与数据写DBF的时机是没啥关系的，那个取决于CKPT.

【解决方法】 分批次处理 ： 例如处理完10000个客户后才提交一次，使用MOD除法函数实现



【效率低的问题脚本】
  --20130802
  --liuyong
  --修正信息: 1-普通预存款余额
  create or replace procedure prc_createacctbook is

  cursor cur_account is
    select *
      from accounten a
     where a.isdefaultid = 1
       and a.businessid = 2
       and not exists (select *
              from acctbooken ac
             where a.accountid_pk = ac.objid
               and ac.balancetypeid_pk = 1);

  V_ACCOUNTID         NUMBER(8);
  V_ACCOUNTCODE       VARCHAR2(50);
  V_ACCOUNTNAME       VARCHAR2(70);
  V_CUSTOMERID        NUMBER(8);
  V_MEN               varchar2(50);
  V_OBJTYPEID         NUMBER(2);
  V_BALANCETYPEID     NUMBER(5);
  V_BALANCETYPENAME   VARCHAR2(50);
  V_account_book_name VARCHAR2(70);

begin

  V_MEN             := 'p20130802LY'; --补丁记录备注
  V_OBJTYPEID       := '1'; --对象类型为账户
  V_BALANCETYPEID   := '1';
  V_BALANCETYPENAME := '普通预存款余额';

  --ADD ACCTBOOK
  for vv_account in cur_account loop

    V_ACCOUNTID   := vv_account.accountid_pk;
    V_ACCOUNTCODE := vv_account.accountcodestr;
    V_ACCOUNTNAME := vv_account.accountnamestr;
    V_CUSTOMERID  := vv_account.customerid_pk;

    --设置余额账本名称
    if (length(concat(V_ACCOUNTNAME, V_BALANCETYPENAME)) > 25) then
      V_account_book_name := V_ACCOUNTNAME;
    else
      V_account_book_name := concat(V_ACCOUNTNAME, V_BALANCETYPENAME);
    end if;

    --添加账本
    insert into acctbooken
      (ACCTBOOKID_PK,
       BALANCETYPEID_PK,
       ACCTBOOKNAMESTR,
       ACCTBOOKCODESTR,
       STARTDT,
       BALANCEID,
       CYCLE_UPPERID,
       CYCLE_LOWERID,
       STATUSID,
       CREATEDT,
       MEM,
       CUSTOMERID,
       OBJTYPEID,
       OBJID)
    values
      (seq_acctbooken.nextval, --ID
       1,
       V_account_book_name, --accountname+typename
       CONCAT(V_ACCOUNTCODE, V_BALANCETYPEID), --accountcode+typecode
       to_date('19700101', 'yyyymmdd'),
       '0',
       '0',
       '0',
       1,
       sysdate,
       V_MEN,
       V_CUSTOMERID,
       V_OBJTYPEID,
       V_ACCOUNTID);

    --添加余额对象关系
    insert into ACCTBALANCEOBJEN
      (ACCBALANCEOBJID_PK,
       ACCTBOOKID_PK,
       OBJTYPEID,
       OBJID,
       MEM,
       CREATEDT,
       STATUSID)
    values
      (SEQ_ACCTBALANCEOBJEN.NEXTVAL,
       seq_acctbooken.currval,
       V_OBJTYPEID,
       V_ACCOUNTID,
       V_MEN,
       sysdate,
       1);


    update payprojecten po
       set po.statusid = 0, po.mem = V_MEN || po.mem
     where po.accountid_pk = V_ACCOUNTID;

    insert into PAYPROJECTEN
      (PAYPROJECTID_PK,
       PAYMETHODID_PK,
       ACCOUNTID_PK,
       ACCTBOOKID_PK,
       PAYTYPEID,
       PRIID,
       STATUSID,
       CREATEDT,
       MEM)
    values
      (SEQ_PAYPROJECTEN.Nextval,
       '111', --paymathod.cash
       V_ACCOUNTID,
       seq_acctbooken.currval,
       1,
       0,
       1,
       sysdate,
       V_MEN);

    COMMIT;
  end loop;

end;
/



【修改后脚本如下】
  --20130802

  create or replace procedure prc_createacctbook is

  cursor cur_account is
    select *
      from accounten a
     where a.isdefaultid = 1
       and a.businessid = 2
       and not exists (select *
              from acctbooken ac
             where a.accountid_pk = ac.objid
               and ac.balancetypeid_pk = 1);

  V_ACCOUNTID         NUMBER(8);
  V_ACCOUNTCODE       VARCHAR2(50);
  V_ACCOUNTNAME       VARCHAR2(70);
  V_CUSTOMERID        NUMBER(8);
  V_MEN               varchar2(50);
  V_OBJTYPEID         NUMBER(2);
  V_BALANCETYPEID     NUMBER(5);
  V_BALANCETYPENAME   VARCHAR2(50);
  V_account_book_name VARCHAR2(70);
  V_NUM               NUMBER;

begin

  V_MEN             := 'p20130802LY'; --补丁记录备注
  V_OBJTYPEID       := '1';
  V_BALANCETYPEID   := '1';
  V_BALANCETYPENAME := '普通预存款余额';
  V_NUM             := 0; --

  --ADD ACCTBOOK
  for vv_account in cur_account loop

    V_ACCOUNTID   := vv_account.accountid_pk;
    V_ACCOUNTCODE := vv_account.accountcodestr;
    V_ACCOUNTNAME := vv_account.accountnamestr;
    V_CUSTOMERID  := vv_account.customerid_pk;

    if (length(concat(V_ACCOUNTNAME, V_BALANCETYPENAME)) > 25) then
      V_account_book_name := V_ACCOUNTNAME;
    else
      V_account_book_name := concat(V_ACCOUNTNAME, V_BALANCETYPENAME);
    end if;


    insert into acctbooken
      (ACCTBOOKID_PK,
       BALANCETYPEID_PK,
       ACCTBOOKNAMESTR,
       ACCTBOOKCODESTR,
       STARTDT,
       BALANCEID,
       CYCLE_UPPERID,
       CYCLE_LOWERID,
       STATUSID,
       CREATEDT,
       MEM,
       CUSTOMERID,
       OBJTYPEID,
       OBJID)
    values
      (seq_acctbooken.nextval, --ID
       1,
       V_account_book_name, --accountname+typename
       CONCAT(V_ACCOUNTCODE, V_BALANCETYPEID), --accountcode+typecode
       to_date('19700101', 'yyyymmdd'),
       '0',
       '0',
       '0',
       1,
       sysdate,
       V_MEN,
       V_CUSTOMERID,
       V_OBJTYPEID,
       V_ACCOUNTID);


    insert into ACCTBALANCEOBJEN
      (ACCBALANCEOBJID_PK,
       ACCTBOOKID_PK,
       OBJTYPEID,
       OBJID,
       MEM,
       CREATEDT,
       STATUSID)
    values
      (SEQ_ACCTBALANCEOBJEN.NEXTVAL,
       seq_acctbooken.currval,
       V_OBJTYPEID,
       V_ACCOUNTID,
       V_MEN,
       sysdate,
       1);


    update payprojecten po
       set po.statusid = 0, po.mem = V_MEN || po.mem
     where po.accountid_pk = V_ACCOUNTID;

    insert into PAYPROJECTEN
      (PAYPROJECTID_PK,
       PAYMETHODID_PK,
       ACCOUNTID_PK,
       ACCTBOOKID_PK,
       PAYTYPEID,
       PRIID,
       STATUSID,
       CREATEDT,
       MEM)
    values
      (SEQ_PAYPROJECTEN.Nextval,
       '111', --paymathod.cash
       V_ACCOUNTID,
       seq_acctbooken.currval,
       1,
       0,
       1,
       sysdate,
       V_MEN);

    --  COMMIT; 注销提交，修改为10000提交一次
    V_NUM := V_NUM + 1;
    IF MOD(V_NUM, 10000) = 0 THEN
       COMMIT; --除10000 为0时提交
    END IF;

  end loop;

end;
/