[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一致.
时间： 2024-10-27 22:53:19