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

问题：有两个存储过程，操作两个表。其中第一个表每天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博客 ” ，链接：http://blog.itpub.net/24419958/viewspace-672506/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24419958/viewspace-672506/