发表于:2007.12.26 13:58
分类: Oracle
出处：http://xsb.itpub.net/post/419/446246
---------------------------------------------------------------
在Oracle10g中可以设置commit的行为来做到在commit之后，控制权立刻返回给用户，而Oracle会在恰当的时候唤醒LGWR，批量更新online redo log文件。
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';
就三种有效.