Asynchronous Commit 【異步提交】

浸心閣 2015-07-18

展開(kāi)全文

某省的系統(tǒng)的commit過(guò)于頻繁，lgwr寫(xiě)平均每次7K，導(dǎo)致大量IO wait，不過(guò)ORACLE 10g提供了Asynchronous Commit 【異步提交】（11G拆分為2個(gè)單獨(dú)的參數(shù) COMMIT_LOGGING 和 COMMIT_WAIT, 分別對(duì)應(yīng) IMMEDIATE | BATCH 和 WAIT | NOWAIT）
經(jīng)過(guò)幾天幾天的對(duì)比測(cè)試，在幾個(gè)重點(diǎn)業(yè)務(wù)區(qū)域均啟用該參數(shù)。雖然效果不錯(cuò)，系統(tǒng)IO明顯下降。不過(guò)還是有點(diǎn)失望，因?yàn)槲以跍y(cè)試環(huán)境中可以提升500倍的lgwr大小，實(shí)際生產(chǎn)環(huán)境只從7k提升到了60K或者80k。
一、lgwr的計(jì)算方法：
avg.redo write size =Redo block written/redo writes*512/1024 (512是redo的大小，512B)
二、異步commit測(cè)試步驟，測(cè)試環(huán)境結(jié)果如下：
1、   lgwr 均值由1k提高到了583K。
COMMIT_WRITE選項(xiàng)
Redo block written
redo writes
avg.redo write size
IMMEDIATE,WAIT
65,347
56
583.4553571
BATCH, NOWAIT
100,706
49,692
1.01330194
2、   log file sync 從top 5中消失。
A、wait的top5
Event
Waits
Time(s)
Avg Wait(ms)
% Total Call Time
Wait Class
log file sync
49,676
192
4
78.4
Commit
log file parallel write
49,691
169
3
68.9
System I/O
CPU time

58

23.4

control file parallel write
233
2
9
.8
System I/O
Log archive I/O
54
2
28
.6
System I/O
         B、nowait 的top5
Event
Waits
Time(s)
Avg Wait(ms)
% Total Call Time
Wait Class
CPU time

21

95.1

log file parallel write
55
2
40
10.0
System I/O
control file parallel write
89
0
5
1.8
System I/O
log file switch completion
10
0
29
1.3
Configuration
db file sequential read
72
0
3
1.1
User I/O
測(cè)試過(guò)程如下：
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時(shí)設(shè)置異步提交
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什么參數(shù)都不帶, 等于NOWAIT,
2) 參數(shù)默認(rèn)是IMMEDIATE, WAIT
3) BATCH和IMMEDIATE速度差不多(為啥?)
4) WAIT產(chǎn)生等待事件, NOWAIT不產(chǎn)生
5) BATCH+NOWAIT最快, IMMEDIATE+WAIT最慢
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
四、通過(guò)參數(shù)設(shè)置異步提交
新增的系統(tǒng)參數(shù)是 COMMIT_WRITE
語(yǔ)法: COMMIT_WRITE = '{IMMEDIATE | BATCH},{WAIT |NOWAIT}'
可以在系統(tǒng)級(jí)或會(huì)話(huà)級(jí)設(shè)置, 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
第因?yàn)樵诘?步設(shè)置了NOWAIT, 所以后面第4,5步也繼承了這個(gè)配置
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
五、PLSQL中的優(yōu)化
PL/SQL 會(huì)自動(dòng)將其中的 COMMIT 優(yōu)化成為"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
只產(chǎn)生了1次等待事件
10gR2版本以前也發(fā)現(xiàn)有異步提交, 見(jiàn)The LGWR dilemma

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶(hù)發(fā)布，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購(gòu)買(mǎi)等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來(lái)自：浸心閣 > 《Asynchronous Commit》

舉報(bào)/認(rèn)領(lǐng)