ITPub博客

首页 > 数字化转型 > ERP > commit_write

commit_write

原创 ERP 作者:xsb 时间:2007-12-26 13:58:41 0 删除 编辑

在Oracle10g中可以设置commit的行为来做到在commit之后,控制权立刻返回给用户,而Oracle会在恰当的时候唤醒LGWR,批量更新online redo log文件。

[@more@]

Ref: http://www.itpub.net/viewthread.php?tid=884483&extra=&page=2

IMMEDIATE - The commit "prods" the LGWR process by sending a message, so that the redo is written imemdiately to the redo logs.

BATCH - The writes to the redo logs are buffered.

WAIT - The commit command is synchronous. It doesn't return until the relevant redo information is written to the online redo log.

NOWAIT - The commit command is asynchronous. It can return before the relevant redo information is written to the online redo log可以有N种组合。


ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='WAIT';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='NOWAIT';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='IMMEDIATE';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='BATCH';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='BATCH,WAIT';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='BATCH,NOWAIT';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='IMMEDIATE,WAIT';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='IMMEDIATE,NOWAIT';

SQL>CREATE TABLE commit_test (
id NUMBER(10),
description VARCHAR2(50),
CONSTRAINT commit_test_pk PRIMARY KEY (id)
);


SQL> DECLARE
PROCEDURE do_loop (p_type IN VARCHAR2) AS
2 3 l_start NUMBER;
4 l_loops NUMBER := 1000;
5 BEGIN
6 EXECUTE IMMEDIATE 'ALTER SESSION SET COMMIT_WRITE=''' || p_type || '''';
7 EXECUTE IMMEDIATE 'TRUNCATE TABLE commit_test';
8
9 l_start := DBMS_UTILITY.get_time;
10 FOR i IN 1 .. l_loops LOOP
11 INSERT INTO commit_test (id, description)
12 VALUES (i, 'Description for ' || i);
COMMIT;
13 14 END LOOP;
15 DBMS_OUTPUT.put_line(RPAD('COMMIT_WRITE=' || p_type, 30) || ': ' || (DBMS_UTILITY.get_time - l_start));
END;
BEGIN
16 17 18 do_loop('WAIT');
19 do_loop('NOWAIT');
do_loop('BATCH');
20 21 do_loop('IMMEDIATE');
do_loop('BATCH,WAIT');
22 23 do_loop('BATCH,NOWAIT');
do_loop('IMMEDIATE,WAIT');
24 25 do_loop('IMMEDIATE,NOWAIT');
END;
26 27 /
COMMIT_WRITE=WAIT : 286
COMMIT_WRITE=NOWAIT : 87
COMMIT_WRITE=BATCH : 53
COMMIT_WRITE=IMMEDIATE : 91
COMMIT_WRITE=BATCH,WAIT : 268
COMMIT_WRITE=BATCH,NOWAIT : 19
COMMIT_WRITE=IMMEDIATE,WAIT : 284
COMMIT_WRITE=IMMEDIATE,NOWAIT : 36

PL/SQL procedure successfully completed.

ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='BATCH,WAIT';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='BATCH,NOWAIT';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='IMMEDIATE,WAIT';

就三种有效.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12402/viewspace-995803/,如需转载,请注明出处,否则将追究法律责任。

上一篇: analyze table
下一篇: update restart
请登录后发表评论 登录
全部评论
  • 博文量
    66
  • 访问量
    3082275