Asynchronous Commit 异步提交

Asynchronous Commit
Oracle 10gR2+ 的异步提交

Oracle 10gR2开始, 增强了提交的功能, 实现异步/批量的提交


1. 异步提交

默认时提交的步骤:
1) 向系统全局区(SGA)中的重做缓冲区(redo log buffer)中写'事务结束(end of transaction)'记录
2) 通知(发送消息到)写日志进程(LGWR), 告诉它刷新重做缓冲区到磁盘
3) 等待磁盘刷新完成. 这就是常见的'日志文件同步'('log file sync')事件

10gR2 COMMIT增加了新选项:
WAIT: 等待相应的重做信息写到在线重做日志文件中后,提交命令才返回(缺省)
NOWAIT: 不等重做信息写到日志中, 提交命令就返回
IMMEDIATE: 写日志进程立刻写重做信息(缺省). 即强制执行一次磁盘 IO.
BATCH: 将重做信息缓冲起来. 写日志进程到时再写重做信息.

虽然提高了性能, 但是一旦系统宕机, 缓冲区中的已提交事务的重做信息将丢失. 如果遭遇磁盘IO错误, 也会丢失重做信息.



2. COMMIT命令

新的 COMMIT 命令增加了如下选项:
COMMIT WRITE IMMEDIATE|BATCH WAIT|NOWAIT;
见WRITE Clause

COMMIT WRITE NOWAIT: 不做前面提到的第3步
COMMIT WRITE BATCH,NOWAIT: 不做第2和3步, 重做记录保留在缓冲区内, 直到其他人提交导致刷新, 或后台事件导致缓冲区异步的刷新
后台事件如下:
缓冲区1/3满
缓冲区充满1M
每3秒

测试:

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最慢


3. 系统初始化参数

新增的系统参数是 COMMIT_WRITE
语法: COMMIT_WRITE = '{IMMEDIATE | BATCH},{WAIT |NOWAIT}'
可以在系统级或会话级设置, ALTER SYSTEM, ALTER SESSION
比如:

SQL> show parameter commit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
commit_point_strength                integer     1
commit_write                         string
max_commit_propagation_delay         integer     0
SQL> alter system set commit_write='batch,nowait';

System altered.

SQL> show parameter commit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
commit_point_strength                integer     1
commit_write                         string      batch,nowait
max_commit_propagation_delay         integer     0
SQL>

11g取消了COMMIT_WRITE (为了兼容仍保留), 拆分为2个单独的参数 COMMIT_LOGGING 和 COMMIT_WAIT, 分别对应 IMMEDIATE | BATCH 和 WAIT | NOWAIT

测试:

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步也继承了这个配置


4. 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



外部链接:
Commit Enhancements in Oracle 10g Database Release 2
10gR2 New Feature: Asynchronous Commit
On setting commit_write
Quantifying Commit Time
Asynchronous Commit - New Feature in Oracle 10GR2 (10.2)
Expert Oracle Database 11g Administration By Sam R. Alapati


-fin-