最近遇到log file sync等待時間，測試了一下非同步提交

一直以來ORACLE對於COMMIT預設採取同步寫事務LOG的方式，也就是說，一旦發出COMMIT命令，那麼必須等待LGWR將事務相關的所有日誌資訊都已經從LOG BUFFER寫出到REDO LOGFILE以後，才會返回發出COMMIT已完成。從10gR2開始，ORACLE推出了一個新的引數COMMIT_WRITE，可以透過調整此引數實現ORACLE在COMMIT時是採用同步或非同步日誌，該引數有以下值：
1.IMMEDIATE: 發出COMMIT命令後，立即將相關日誌資訊從LOG BUFFER寫出到REDO LOGFILE，也就是每次提交時都必須做一次磁碟I/O操作，預設選項。
2.WAIT：在日誌資訊從LOG BUFFER寫出到REDO LOGFILE的過程中，必須等待LGWR將所有事務相關的所有日誌資訊都已經從LOG BUFFER寫出到REDO LOGFILE以後，才會返回發出COMMIT已完成，預設選項。
3.NOWAIT：發出COMMIT命令後，不管日誌資訊從LOG BUFFER寫出到REDO LOGFILE是否完成，立即返回COMMIT完成。
4.BATCH：ORACLE會對日誌資訊進行緩衝，ORACLE會按照自己特定的規則將日誌資訊從LOG BUFFER批次寫出到REDO LOGFILE，也就是說,多個I/O操作將打包成一個批次進行處理，以提高效能。
IMMEDIATE和WAIT是傳統的提交方式也是預設的提交方式，NOWAIT和BATCH可以說是10gR2的新特性，ORACLE利用此新特性解決在繁忙的系統中LGWR寫是資料庫瓶頸的問題，當然，同步COMMIT也可以BATCH，非同步COMMIT也可以將日誌資訊立即寫出，以上四個引數值可以自由組合，如下：
 
COMMIT_WRITE='{ IMMEDIATE | BATCH } , { WAIT | NOWAIT }'
預設情況是:
 
COMMIT_WRITE=’IMMEDIATE,WAIT’
所以COMMIT_WRITE有下面幾種組合：
 
COMMIT_WRITE='IMMEDIATE,WAIT'
COMMIT_WRITE='IMMEDIATE,NOWAIT'
COMMIT_WRITE='BATCH,WAIT'
COMMIT_WRITE='BATCH,NOWAIT'
由於非同步COMMIT不能確保事務的相關日誌資訊已經全部寫出到REDO LOGFILE當中，一旦例項崩潰，可能導致已經COMMIT的事務無法恢復，所以一定看程式是否適合使用非同步COMMIT，COMMIT_WRITE是動態引數，可以在程式做適合開啟非同步COMMIT的時候再使用，使用後應在程式不需要使用非同步COMMIT的時候儘快關閉非同步COMMIT。比如我們的系統，在批次匯入條目資料和圖片資料的時候，可以開啟非同步COMMIT特性，在匯入資料完成後，關閉非同步COMMIT特性，這樣可以解決程式在載入資料的時候，大量的LOG FILE SYNC等待事件，即使此時資料庫崩潰，可以刪除這個批次的資料，重新匯入一次。（當然，資料庫崩潰的可能不是很大，要不還要我們DBA幹嘛）。
下面是我在本機10gR2版本資料庫做的簡單演示：
 
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
建立測試表T_COMMIT。
 
SQL> create table t_commit (id number, name varchar2(30));
表已建立。
將COMMIT_WRITE引數修改為IMMEDIATE,WAIT，這也是預設值。
 
SQL> alter system set commit_write='immediate,wait';
系統已更改。
向T_COMMIT表中插入10000條資料，沒插入一條記錄提交一次，記錄操作的時間。
 
SQL> begin
  2  for i in 1..10000 loop
  3  insert into t_commit values (i, 'a'||i);
  4  commit;
  5  end loop;
  6  end;
  7  /
PL/SQL 過程已成功完成。
已用時間:  00: 00: 05.67
在COMMIT_WRITE引數修改為IMMEDIATE,WAIT的情況下，向資料庫中插入10000條資料需要5.67秒，檢視REDO的相關資訊。
 
SQL> select * from v$sysstat where statistic# in (90,91,138,140);
STATISTIC#   NAME                 CLASS      VALUE      STAT_ID
----------   ------------------- ------ ----------   ----------
        90   redo synch writes        8      10017   1439995281
        91   redo synch time          8       2671   4215815172
       138   redo writes              2      10027   1948353376
       140   redo write time          2       2131   3094453259
可見，在這種模式下，redo synch writes一萬次，redo writes一萬次，這就有可能產生 log file sync等待事件，嚴重影響效能，這也是程式不要修改一條資料就提交一次，建議批次提交的原因。
再測試下COMMIT_WRITE引數的值為IMMEDIATE,NOWAIT模式下，插入10000條記錄，也是每插入一條記錄就提交一次，記錄時間。
 
SQL> alter system set commit_write='immediate,nowait';
系統已更改。
SQL> begin
  2  for i in 1..10000 loop
  3  insert into t_commit values (i, 'a'||i);
  4  commit;
  5  end loop;
  6  end;
  7  /
PL/SQL 過程已成功完成。
已用時間:  00: 00: 01.14
在COMMIT_WRITE引數的值為IMMEDIATE,NOWAIT的情況下，同樣插入10000條記錄，只用1.14秒，看下REDO的相關資訊。
 
SQL> select * from v$sysstat where statistic# in (90, 91, 138, 140);
STATISTIC#   NAME                 CLASS      VALUE      STAT_ID
----------   ------------------- ------ ----------   ----------
        90 redo synch writes          8      10017   1439995281
        91 redo synch time            8       2671   4215815172
       138 redo writes                2      16805   1948353376
       140 redo write time            2       4003   3094453259
可以看到，這次redo synch writes並沒有增長，redo writes只增長了6千多次，也就是LOWR只寫了6千多次就已經將LOG BUFFER的相關資訊寫到了REDO LOGFILE，而不是寫10000次，還有redo synch time沒有發生，而redo write time幾乎沒有變化，還是2000多毫秒，這種模式需要注意的是，一旦提交完成，而LOWR還沒有將所有的REDO BUFFER裡面相關的資訊寫到REDO LOGFILE的時候資料庫崩潰，已提交的資料可能會找不回來，這也是這種模式需要慎用的原因。
再測試下COMMIT_WRITE引數的值為BATCH，WAIT模式下，同樣插入10000條記錄，需要的時間。
 
SQL> alter system set commit_write='batch,wait';
系統已更改。
SQL> begin
  2  for i in 1..10000 loop
  3  insert into t_commit values (i, 'a'||i);
  4  commit;
  5  end loop;
  6  end;
  7  /
PL/SQL 過程已成功完成。
已用時間:  00: 00: 06.20
這次用了6.20秒，竟然比IMMEDIATE,WAIT模式下用了5.67秒的時間還要長，這是由於在COMMIT以後, 客戶端需要等待LGWR完成的通知，由於BATCH模式會將其他SESSION的日誌資訊一起打包批次從LOG BUFFER寫入REDO LOGFILE，所以它會查詢和等待其他事務的日誌資訊，再看下BATCH，WAIT模式下LGWR寫REDO的相關資訊。
 
SQL> select * from v$sysstat where statistic# in (90, 91, 138, 140);
STATISTIC#   NAME                 CLASS      VALUE      STAT_ID
----------   ------------------- ------ ----------   ----------
        90 redo synch writes          8      20023   1439995281
        91 redo synch time            8       6084   4215815172
       138 redo writes                2      26813   1948353376
       140 redo write time            2       6167   3094453259
對照IMMEDIATE,NOWAIT模式的資訊可以發現，redo synch writes寫了10000次，redo writes也寫了10000次，redo write time一直很穩定，還是2000毫秒左右，但是redo synch time卻用了3400毫秒左右，從這個實驗也可以看出redo synch對效能的影響，這傢伙用的時間比redo write用的時間都多。
看下BATCH，NOWAIT模式的效果怎麼樣。
 
SQL> alter system set commit_write='batch,nowait';
系統已更改。
SQL> begin
  2  for i in 1..10000 loop
  3  insert into t_commit values (i, 'a'||i);
  4  commit;
  5  end loop;
  6  end;
  7  /
PL/SQL 過程已成功完成。
已用時間:  00: 00: 01.01
單SESSION情況下，BATCH，NOWAIT模式和IMMEDIATE，NOWAIT用時差不多，看下LGWR寫REDO的相關資訊。
SQL> select * from v$sysstat where statistic# in (90, 91, 138, 140);
STATISTIC#   NAME                 CLASS      VALUE      STAT_ID
----------   ------------------- ------ ----------   ----------
        90 redo synch writes          8      20024   1439995281
        91 redo synch time            8       6084   4215815172
       138 redo writes                2      26835   1948353376
       140 redo write time            2       6246   3094453259
看看，這傢伙夠狠吧，redo synch writes可以說沒有發生，redo synch time當然也就沒有，redo writes只寫了只寫了20次左右，redo write time只有幾十毫秒，BATCH，NOWAIT將事物的日誌資訊打包，再由LGWR從LOG BUFFER寫到REDO LOGFILE，也就是LOGWR只寫了20次左右就寫完了提交10000次的日誌資訊，而且這個時間由以前的2000毫秒降到了幾十毫秒，這只是在單SESSION環境下，如果在多SESSION，高併發的環境下，效果會更好一些，可見ORACLE對高併發應用LGWR寫瓶頸的改善還是灰常灰常牛X的，但同樣值得關注的還是高效能帶來的高風險，還好COMMIT_WRITE是動態引數，可以隨意調整而不需要重啟資料庫，這也是我在考慮我們的應用是否使用COMMIT_WRITE引數的原因。
還是看下BATCH模式對高併發環境LGWR寫REDO的資訊，為了演示更直觀，本實驗使用BATCH,WAIT模式，而且重啟了資料庫，清空v$sysstat檢視的資訊，本實驗一共開了5個SESSION，每個SESSION都執行上述實驗的SQL，都插入10000條記錄，看下LGWR寫REDO的資訊。
SQL> alter system set commit_write='batch,nowait';
系統已更改。
5個SESSION都同時執行下面的SQL，每個SESSION都插入10000條記錄。
 
SQL> begin
  2  for i in 1..10000 loop
  3  insert into t_commit values (i, 'a'||i);
  4  commit;
  5  end loop;
  6  end;
  7  /
PL/SQL 過程已成功完成。
然後檢視LGWR寫REDO的資訊。
 
SQL> select * from v$sysstat where statistic# in (90, 91, 138, 140);
STATISTIC#   NAME                 CLASS      VALUE      STAT_ID
----------   ------------------- ------ ----------   ----------
        90 redo synch writes          8      50010   1439995281
        91 redo synch time            8      52530   4215815172
       138 redo writes                2      19336   1948353376
       140 redo write time            2       8747   3094453259
5個SESSION都插入10000條資料，redo synch writes5萬次， redo synch time高達52530毫秒，但redo writes並不是5萬次，而是19336次，這是由於BATCH將其他SESSION的日誌資訊打包處理了。
如果是BATCH，NOWAIT模式，這個結果讓我很震驚，也讓我想起了2011年的經典臺詞，至於你信嗎？反正我信了！下面是重啟資料庫後，BATCH，NOWAIT模式下5個SESSION同時插入10000條資料的LGWR寫REDO日誌的資訊。
 
SQL> select * from v$sysstat where statistic# in (90, 91, 138, 140);
STATISTIC#   NAME                 CLASS      VALUE      STAT_ID
----------   ------------------- ------ ----------   ----------
        90 redo synch writes          8          6   1439995281
        91 redo synch time            8          2   4215815172
       138 redo writes                2         68   1948353376
       140 redo write time            2        489   3094453259
redo synch writes只有6次，還可能不是由於插入資料產生的，可能是ORACLE內部的呼叫引起的，redo writes只有68次，就寫完了所有的日誌資訊，而且只用了489毫秒！震撼嗎？
COMMIT_WRITE是10gR2引入的新特性，11g版本的資料庫已經不在使用COMMIT_WRITE引數，11g使用COMMIT_WAIT和COMMIT_LOGGING取替了COMMIT_WRITE引數，如果在11g的資料庫中設定了COMMIT_WRITE引數，重啟後，將會報ORA-32004錯誤。
 
SQL> alter system set commit_write=immediate,nowait;
系統已更改。
SQL> shutdown immediate
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified
for RDBMS instance
ORACLE 例程已經啟動。
Total System Global Area  313860096 bytes
Fixed Size                  1374304 bytes
Variable Size             239077280 bytes
Database Buffers           67108864 bytes
Redo Buffers                6299648 bytes
資料庫裝載完畢。
資料庫已經開啟。
ORACLE會說這是個老掉牙的引數了，我現在已經不用了，你設定這個引數就相當於在引數檔案裡寫了一條垃圾，我根本不用它，但是這個錯誤不影響資料庫的啟動和執行。
被COMMIT_LOGGING和COMMIT_WAIT取代的COMMIT_WRITE引數在11g有所增強，COMMIT_LOGGING引數有IMMEDIATE和BATCH兩個值可選，COMMIT_WAIT引數有WAIT、NOWAIT和FORCE_WAIT三個值可以選擇，ORACLE的館方文件說COMMIT_LOGGING和COMMIT_WAIT沒有預設值，是說SQLPLUS裡看不到預設值，實際上ORACLE的COMMIT機制預設是IMMEDIATE和FORCE_WAIT。
 
SQL> show parameter commit_
NAME                       TYPE                VALUE
------------------------   -----------------   --------
commit_logging             string
commit_point_strength      integer             1
commit_wait                string
commit_write               string
關於11g關於這方面的效能測試不再演示，摘錄下11g官方文件有關引數的介紹：
 
這部分主要對COMMIT_LOGGING引數的簡單介紹，主要說COMMIT_LOGGING是個新引進的引數，有IMMEDIATE和BATCH兩個值可以選擇，可以在會話級和系統級進行設定，RAC的每個節點都可以單獨設定，還說如果在COMMIT_WAIT引數設定FORCE_WAIT之後修改COMMIT_LOGGING引數，那麼FORCE_WAIT將失效。
 
COMMIT_WAIT is an advanced parameter used to control when the redo for a commit is flushed to the redo logs.
Be aware that the NOWAIT option can cause a failure that occurs after the database receives the commit message, but before the redo log records are written. This can falsely indicate to a transaction that its changes are persistent. Also, it can violate the durability of ACID (Atomicity, Consistency, Isolation, Durability) transactions if the database shuts down unexpectedly.
If the parameter is set to FORCE_WAIT, the default behavior (immediate flushing of the redo log buffer with wait) is used. If this is a system setting, the session level and transaction level (COMMIT_WRITE) options will be ignored. If this is a session level setting, the transaction level options will be ignored. If COMMIT_WAIT is altered after it has been set to FORCE_WAIT, then the FORCE_WAIT option is no longer valid.
一樣，這部分也是對COMMIT_WAIT引數的簡單介紹，主要說COMMIT_WAIT引數有WAIT、NOWAIT和FORCE_WAIT三個值可以選擇，可以在會話級和系統級做設定，RAC的每個節點都可以單獨設定，還說了下NOWAIT可能會由於虛假提交丟失資料。
 
COMMIT_WRITE is an advanced parameter used to control how redo for transaction commits is written to the redo logs. The IMMEDIATE and BATCH options control how redo is batched by Log Writer. The WAIT and NOWAIT options control when the redo for a commit is flushed to the redo logs.
Note:
The COMMIT_WRITE parameter is deprecated. It is retained for backward compatibility only. It is replaced by the COMMIT_LOGGING and COMMIT_WAIT parameters.
這部分是對COMMIT_WRITE引數的簡單介紹，主要是說COMMIT_WRITE是個過度引數，在11g已經被COMMIT_LOGGING引數和COMMIT_WAIT引數所取代。