commit_write

在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';

就三種有效.