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

简介: [20130607]PL/SQL存储过程的commit提交问题.txt昨天在看别人写的存储过程的时候,发现程序代码里面不好的写法,就是把commit写在循环体内,这样写按照以前应该会产生很大的redo日志,主要是redo waste也会增加,但是我在11G下测试,情况好像不一样.
[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一致.
 
目录
相关文章
|
1月前
|
存储 SQL Oracle
PL/SQL存储过程的使用
PL/SQL存储过程的使用
31 1
|
1月前
|
存储 SQL 缓存
4.2.1 SQL语句、索引、视图、存储过程
4.2.1 SQL语句、索引、视图、存储过程
|
1月前
|
SQL 存储 关系型数据库
sql语句,索引,视图,存储过程
sql语句,索引,视图,存储过程
18 0
|
2月前
|
存储 SQL Oracle
|
2月前
|
存储 SQL 定位技术
数据库基础(六):T-SQL语言程序设计、游标定义与使用及存储过程创建与信息查找
数据库基础(六):T-SQL语言程序设计、游标定义与使用及存储过程创建与信息查找
|
2月前
|
存储 SQL 数据库
数据库第十二次作业 存储过程(PL/SQL语句集)的应用
数据库第十二次作业 存储过程(PL/SQL语句集)的应用
25 0
|
3月前
|
SQL 存储 Oracle
Oracle如何使用PL/SQL调试存储过程
Oracle如何使用PL/SQL调试存储过程
|
6月前
|
存储 SQL 关系型数据库
【MySQL】探索MySQL存储过程的魔力,初学者的数据库编程秘笈(内含实战SQL脚本)下
【MySQL】探索MySQL存储过程的魔力,初学者的数据库编程秘笈(内含实战SQL脚本)
136 1
|
6月前
|
存储 SQL 关系型数据库
【MySQL】探索MySQL存储过程的魔力,初学者的数据库编程秘笈(内含实战SQL脚本)上
【MySQL】探索MySQL存储过程的魔力,初学者的数据库编程秘笈(内含实战SQL脚本)
72 2
|
6月前
|
存储 SQL
sql_存储过程、函数、分支、循环
sql_存储过程、函数、分支、循环
78 0