问题：有两个存储过程，操作两个表。其中第一个表每天85W左右数据，一个月接近3000W条数据。另外一个表每天50W左右数据，一个月1500W条数据。每个月job处理一次。
现在看到存储过程中，每次数据插入都有提交操作，会写入redo log file，引起磁盘IO。故测试下多次提交与分次提交性能对比。

SQL> create table t as select * from all_objects;
SQL> select count(1) from t;

COUNT(1)
----------
50020

原存储过程中一条数据是接近36Byte。因为估算失误，新建测试表只有10byte，不过对于测试没有太大影响。

测试用procedure

create or replace procedure test2 is
cursor mycursor is
select OWNER , CREATED,LAST_DDL_TIME from t ;

--v_number number(8);
v_owner varchar2(30);
v_created date;
v_ddl_time date;
v_sql varchar2(1000);

begin
-- DBMS_OUTPUT.PUT_LINE('hello0');
execute immediate 'TRUNCATE TABLE ttest';
execute immediate 'insert into retime values(to_date(''' ||
                     to_char(sysdate, 'yyyy-mm-dd HH24:mi:ss') || ''',''yyyy-mm-dd HH24:mi:ss''))' ;
   COMMIT;
-- DBMS_OUTPUT.PUT_LINE('hello1');
for v_pos in mycursor loop
   -- DBMS_OUTPUT.PUT_LINE('hello2');
    v_owner:=v_pos.owner;
    v_created:=v_pos.created;
    v_ddl_time:=v_pos.last_ddl_time;
    --DBMS_OUTPUT.PUT_LINE(v_owner);
    v_sql:='insert into ttest values('''|| v_owner || ''',to_date('''||
                     to_char(v_created, 'yyyy-mm-dd HH24:mi:ss') || ''',''yyyy-mm-dd HH24:mi:ss''), to_date(''' ||
                     to_char(v_ddl_time, 'yyyy-mm-dd HH24:mi:ss') || ''',''yyyy-mm-dd HH24:mi:ss''))' ;

    --DBMS_OUTPUT.PUT_LINE(v_sql);
    execute immediate v_sql;
   -- COMMIT; --每次提交用commit
    -- DBMS_OUTPUT.PUT_LINE('hello3');
end loop;
--DBMS_OUTPUT.PUT_LINE('hello4');
    execute immediate 'insert into retime values(to_date(''' ||
                     to_char(sysdate, 'yyyy-mm-dd HH24:mi:ss') || ''',''yyyy-mm-dd HH24:mi:ss''))' ;
commit;
exception
    when others then
-- DBMS_OUTPUT.PUT_LINE('hello5');
    if v_sql <> '' then
      execute immediate 'insert into retime values(to_date(''' ||
                     to_char(sysdate, 'yyyy-mm-dd HH24:mi:ss') || ''',''yyyy-mm-dd HH24:mi:ss''))' ;
    end if;
end test2;

两个测试只是commit不同而已。
测试结果：

SQL> select to_char(retime,'yyyy-mm-dd HH24:mi:ss') from retime;

TO_CHAR(RETIME,'YYYY-MM-DDHH24:MI:SS')
--------------------------------------
2010-09-03 16:49:42
2010-09-03 16:50:11

2010-09-03 16:48:18

一次提交：2010-09-03 16:50:11-2010-09-03 16:49:42=29S
每次提交：2010-09-03 16:48:18-2010-09-03 16:47:34=44S
5W条数据差别是15S左右。
仅作数量级估算4500W/5W*15S=6H。

仅此一项、处理时间可以减少6个小时左右。磁盘IO对性能影响巨大。

ITPub博客

存储过程中大量数据插入性能问题（多次提交引起磁盘IO）

最新文章