异步提交oracle,Asynchronous Commit 【异步提交】

User I/O测试过程如下：

SQL> ALTER SYSTEM SET COMMIT_WRITE = IMMEDIATE,WAIT ;

System altered.

SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

PL/SQL procedure successfully completed.

SQL> begin

2    for x in (select * from all_objects)

3     loop

4      insert into wwm2 values x;

5      commit;

6   end loop;

7   end;

8  /

PL/SQL procedure successfully completed.

SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

PL/SQL procedure successfully completed.

SQL> ALTER SYSTEM SET COMMIT_WRITE = BATCH, NOWAIT;

System altered.

SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

PL/SQL procedure successfully completed.

SQL> begin

2    for x in (select * from all_objects)

3     loop

4      insert into wwm2 values x;

5      commit;

6   end loop;

7   end;

8  /

PL/SQL procedure successfully completed.

SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

PL/SQL procedure successfully completed.

SQL> select count(*) from wwm2;

COUNT(*)

----------

396850

SQL> select count(*) from all_objects;

COUNT(*)

----------

49634

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@localhost ~]$ exit

logout

[root@localhost ~]# exit

logout

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

三、commit时设置异步提交

CREATE TABLE commit_test (

id           NUMBER(10),

description  VARCHAR2(50),

CONSTRAINT commit_test_pk PRIMARY KEY (id)

);

CONN A/A

SET SERVEROUTPUT ON

DECLARE

function get_waits(p_event in varchar2) return number

is

l_waits  NUMBER;

begin

select total_waits

into l_waits

from v$session_event

where event = p_event

and sid = (select sid from v$mystat where rownum=1);

return l_waits;

exception

when no_data_found then return 0;

end;

PROCEDURE do_loop (p_type  IN  VARCHAR2) AS

l_start  NUMBER;

l_loops  NUMBER := 1000;

l_lfs    NUMBER;

BEGIN

EXECUTE IMMEDIATE 'TRUNCATE TABLE commit_test';

l_lfs := get_waits('log file sync');

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 ' ' THEN COMMIT;

WHEN 'WRITE' THEN COMMIT WRITE;

WHEN 'WRITE WAIT' THEN COMMIT WRITE WAIT;

WHEN 'WRITE NOWAIT' THEN COMMIT WRITE NOWAIT;

WHEN 'WRITE BATCH' THEN COMMIT WRITE BATCH;

WHEN 'WRITE IMMEDIATE' THEN COMMIT WRITE IMMEDIATE;

WHEN 'WRITE BATCH WAIT' THEN COMMIT WRITE BATCH WAIT;

WHEN 'WRITE BATCH NOWAIT' THEN COMMIT WRITE BATCH NOWAIT;

WHEN 'WRITE IMMEDIATE WAIT' THEN COMMIT WRITE IMMEDIATE WAIT;

WHEN 'WRITE IMMEDIATE NOWAIT' THEN COMMIT WRITE IMMEDIATE NOWAIT;

END CASE;

END LOOP;

DBMS_OUTPUT.put_line(RPAD('COMMIT ' || p_type, 30)

|| ': ' || (DBMS_UTILITY.get_time - l_start)

|| ': ' || (get_waits('log file sync') - l_lfs)

);

END;

BEGIN

do_loop(' ');

do_loop('WRITE');

do_loop('WRITE WAIT');

do_loop('WRITE NOWAIT');

do_loop('WRITE BATCH');

do_loop('WRITE IMMEDIATE');

do_loop('WRITE BATCH WAIT');

do_loop('WRITE BATCH NOWAIT');

do_loop('WRITE IMMEDIATE WAIT');

do_loop('WRITE IMMEDIATE NOWAIT');

END;

/

COMMIT                        : 19: 0

COMMIT WRITE                  : 151: 1000

COMMIT WRITE WAIT             : 150: 1000

COMMIT WRITE NOWAIT           : 20: 0

COMMIT WRITE BATCH            : 151: 1000

COMMIT WRITE IMMEDIATE        : 152: 1000

COMMIT WRITE BATCH WAIT       : 151: 1000

COMMIT WRITE BATCH NOWAIT     : 15: 0

COMMIT WRITE IMMEDIATE WAIT   : 153: 1000

COMMIT WRITE IMMEDIATE NOWAIT : 20: 0

可以看出

1) COMMIT什么参数都不带, 等于NOWAIT,

2) 参数默认是IMMEDIATE, WAIT

3) BATCH和IMMEDIATE速度差不多(为啥?)

4) WAIT产生等待事件, NOWAIT不产生

5) BATCH+NOWAIT最快, IMMEDIATE+WAIT最慢

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

四、通过参数设置异步提交

新增的系统参数是 COMMIT_WRITE

语法: COMMIT_WRITE = '{IMMEDIATE | BATCH},{WAIT |NOWAIT}'

可以在系统级或会话级设置, ALTER SYSTEM, ALTER SESSION

conn a/a

SET SERVEROUTPUT ON

DECLARE

function get_waits(p_event in varchar2) return number

is

l_waits  NUMBER;

begin

select total_waits

into l_waits

from v$session_event

where event = p_event

and sid = (select sid from v$mystat where rownum=1);

return l_waits;

exception

when no_data_found then return 0;

end;

PROCEDURE do_loop (p_type  IN  VARCHAR2) AS

l_start  NUMBER;

l_loops  NUMBER := 1000;

l_lfs    NUMBER;

BEGIN

if p_type is not null then

EXECUTE IMMEDIATE 'ALTER SESSION SET COMMIT_WRITE=''' || p_type || '''';

end if;

EXECUTE IMMEDIATE 'TRUNCATE TABLE commit_test';

l_lfs := get_waits('log file sync');

l_start := DBMS_UTILITY.get_time;

FOR i IN 1 .. l_loops LOOP

INSERT INTO commit_test (id, description)

VALUES (i, 'Description for ' || i);

COMMIT;

END LOOP;

DBMS_OUTPUT.put_line(RPAD('COMMIT_WRITE=' || p_type, 30)

|| ': ' || (DBMS_UTILITY.get_time - l_start)

|| ': ' || (get_waits('log file sync') - l_lfs)

);

END;

BEGIN

do_loop(NULL);

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=                 : 20: 0

COMMIT_WRITE=WAIT             : 151: 1000

COMMIT_WRITE=NOWAIT           : 19: 0

COMMIT_WRITE=BATCH            : 14: 0

COMMIT_WRITE=IMMEDIATE        : 19: 0

COMMIT_WRITE=BATCH,WAIT       : 150: 1000

COMMIT_WRITE=BATCH,NOWAIT     : 15: 0

COMMIT_WRITE=IMMEDIATE,WAIT   : 153: 1000

COMMIT_WRITE=IMMEDIATE,NOWAIT : 20: 0

第因为在第3步设置了NOWAIT, 所以后面第4,5步也继承了这个配置

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

五、PLSQL中的优化

PL/SQL 会自动将其中的 COMMIT 优化成为"COMMIT WRITE NOWAIT", 只有最后一次 COMMIT 才是真正的"COMMIT"

conn a/a

set serveroutput on size unlimited

truncate table commit_test;

select total_waits

from v$session_event

where event = 'log file sync'

and sid = (select sid from v$mystat where rownum=1);

declare

l_loops number := 1000;

begin

FOR i IN 1 .. l_loops LOOP

INSERT INTO commit_test (id, description)

VALUES (i, 'Description for ' || i);

COMMIT;

END LOOP;

end;

/

select total_waits

from v$session_event

where event = 'log file sync'

and sid = (select sid from v$mystat where rownum=1);

TOTAL_WAITS

-----------

1

SQL>   2    3    4    5    6    7    8    9   10

PL/SQL procedure successfully completed.

SQL>   2    3    4

TOTAL_WAITS

-----------

2

只产生了1次等待事件

10gR2版本以前也发现有异步提交, 见The LGWR dilemma