[20130607]PL/SQL儲存過程的commit提交問題.txt

lfree發表於2013-06-07
SQL儲存過程MIT
[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找到如下連結:

--我修改一點,加入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一致.
 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-763439/，如需轉載，請註明出處，否則將追究法律責任。

相關文章