[20130607]PL/SQL存储过程的commit提交问题.txt

昨天在看别人写的存储过程的时候,发现程序代码里面不好的写法,就是把commit写在循环体内,这样写按照以前应该会产生很大的redo日
志,主要是redo waste也会增加,但是我在11G下测试,情况好像不一样.

1.建立测试环境:

SQL> @ver

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table t (a number,b varchar2(10));

$ cat viewredo.sql

SELECT b.NAME, a.statistic#, a.VALUE

  FROM v$mystat a, v$statname b

 WHERE b.NAME IN ('redo size', 'redo wastage') AND a.statistic# = b.statistic#;

2.测试1:(commit在循环体内)

set timing on

@viewredo.sql

begin

for i in 1..10000 loop

insert into t values (i,'test');

commit;

end loop;

end;

/

@viewredo

@viewredo.sql

NAME                 STATISTIC#      VALUE

-------------------- ---------- ----------

user commits                  6          0

redo size                   178        728

redo wastage                183          0

@viewredo.sql

NAME                  STATISTIC#      VALUE

-------------------- ---------- ----------

user commits                  6      10000

redo size                   178    4863032

redo wastage                183          0

--Elapsed: 00:00:01.32,redo size=4863032-728=4862304.

3.测试2:(commit在循环体外)

set timing on

@viewredo.sql

begin

for i in 1..10000 loop

insert into t values (i,'test');

end loop;

commit;

end;

/

@viewredo.sql

NAME                 STATISTIC#      VALUE

-------------------- ---------- ----------

user commits                  6          0

redo size                   178        728

redo wastage                183          0

NAME                 STATISTIC#      VALUE

-------------------- ---------- ----------

user commits                  6          1

redo size                   178    2536896

redo wastage                183          0

--Elapsed: 00:00:00.56,redo size=2536896-728=2536168.

--对比发现时间上并没有什么差别,当然redo size前面几乎比后面的大1倍,不知道为什么这个版本redo wastage都是0.
--我记忆里面以前在8i下做过测试,测试1应该需要许多时间.不可能这么快完成.

4.测试3

--突然想起来10g下提交可以改成如下:

set timing on

@viewredo.sql

begin 

for i in 1..10000 loop 

insert into t values (i,'test'); 

commit write wait; 

end loop; 

end; 

/

@viewredo.sql

NAME                 STATISTIC#      VALUE

-------------------- ---------- ----------

user commits                  6          0

redo size                   178        728

redo wastage                183          0

NAME                 STATISTIC#      VALUE

-------------------- ---------- ----------

user commits                  6      10000

redo size                   178    5527876

redo wastage                183          0

--Elapsed: 00:01:01.14.redo size=5527876-728=5527148.


5.google找到如下链接:
http://www.oracle-base.com/articles/10g/commit-10gr2.php
--我修改一点,加入commit的比较.

CREATE TABLE commit_test (

  id           NUMBER(10),

  description  VARCHAR2(50),

  CONSTRAINT commit_test_pk PRIMARY KEY (id)

);

SET SERVEROUTPUT ON

DECLARE

  PROCEDURE do_loop (p_type  IN  VARCHAR2) AS

    l_start  NUMBER;

    l_loops  NUMBER := 1000;

  BEGIN

    EXECUTE IMMEDIATE 'TRUNCATE TABLE commit_test';

    l_start := DBMS_UTILITY.get_time;

    FOR i IN 1 .. l_loops LOOP

      INSERT INTO commit_test (id, description)

      VALUES (i, 'Description for ' || i);

      CASE p_type

        WHEN 'WAIT'             THEN COMMIT WRITE WAIT;

        WHEN 'NOWAIT'           THEN COMMIT WRITE NOWAIT;

        WHEN 'BATCH'            THEN COMMIT WRITE BATCH;

        WHEN 'IMMEDIATE'        THEN COMMIT WRITE IMMEDIATE;

        WHEN 'BATCH,WAIT'       THEN COMMIT WRITE BATCH WAIT;

        WHEN 'BATCH,NOWAIT'     THEN COMMIT WRITE BATCH NOWAIT;

        WHEN 'IMMEDIATE,WAIT'   THEN COMMIT WRITE IMMEDIATE WAIT;

        WHEN 'IMMEDIATE,NOWAIT' THEN COMMIT WRITE IMMEDIATE NOWAIT;

        else  COMMIT;

      END CASE;

    END LOOP;

    DBMS_OUTPUT.put_line(RPAD('COMMIT WRITE ' || p_type, 30) || ': ' || (DBMS_UTILITY.get_time - l_start));

  END;

BEGIN

  do_loop('other');

  do_loop('WAIT');

  do_loop('NOWAIT');

  do_loop('BATCH');

  do_loop('IMMEDIATE');

  do_loop('BATCH,WAIT');

  do_loop('BATCH,NOWAIT');

  do_loop('IMMEDIATE,WAIT');

  do_loop('IMMEDIATE,NOWAIT');

END;

/

COMMIT WRITE other            : 18

COMMIT WRITE WAIT             : 680

COMMIT WRITE NOWAIT           : 18

COMMIT WRITE BATCH            : 780

COMMIT WRITE IMMEDIATE        : 752

COMMIT WRITE BATCH,WAIT       : 901

COMMIT WRITE BATCH,NOWAIT     : 19

COMMIT WRITE IMMEDIATE,WAIT   : 991

COMMIT WRITE IMMEDIATE,NOWAIT : 16

PL/SQL procedure successfully completed.

--很明显commit与COMMIT WRITE WAIT一致.