PL/SQL 事務持久化異常 / PL/SQL commit優化

   傳統情況下，當使用者發出commit後，使用者會話會等待log file sync直到lgwr寫完成。LGWR寫完成後，通知處於等待log file sync的會話繼續處理後面的操作。這個機制保障了事務的永續性，滿足了事務ACID的D。但是PL/SQL不是這麼工作的：PL/SQL裡的commit 操作不會等待lgwr寫完成就可以繼續處理後面的操作。簡單的看個例子：

CODE:

begin
for r in (select id from t1 where mod(id, 20) = 0) loop
update t1 set small_no = small_no + .1 where id = r.id;
commit;
end loop;
end;
/上面的程式碼邏輯非常簡單，每20行更新一條記錄，並且提交一次，表裡一共500條資料，一共需要提交25次。執行完成後，我們檢查這個會話的統計資料，我們認為的統計資訊，應該象下面展示的：

CODE:

user commits (session statistic)      25
messages sent (session statistic)     25
redo synch writes (session statistic) 25
log file sync (session events)        25
messages received (lgwr statistic)    25
redo writes (lgwr statistic)          25
log file parallel write (lgwr events) 25
但是實際情況和我們認為的很不一樣：
user commits (session statistic)      25
messages sent (session statistic)      6
redo synch writes(session statistic)   1
log file sync (session events)         1
messages received (lgwr statistic)     6
redo writes (lgwr statistic)           6
log file parallel write (lgwr events)  6     Lgwr僅僅寫了6次（log file parallel write），使用者會話僅僅等待了log file sync一次。那意味著會話發出commit命令後，並沒有停下來等待lgwr寫完成，就繼續處理後面的事務了。使用者會話沒有遵守事務的持久化原則！！如果例項在PL/SQL處理的過程中crash，那麼某些提交的事務是不可恢復的。Oracle對此有一個貌似合理的解釋，在PL/SQL沒有處理完畢之前，你不知道已經提交了多次。Oracle不會使他們可恢復，只有在PL/SQL結束的時候，增加redo sync writes次數和進入log file sync等待。在進行PL/SQL處理期間，不停的檢視等待事件，後臺看不到任何的log file sync等待。值得注意的是，如果PL/SQL裡包含了DBLINK，那麼就會使用傳統的提交方式，不會產生出這樣的“異常”。還有就是統計資料裡顯示了會話總共向lgwr傳送了6次message sent請求（請求寫日誌），lgwr也接受到了6次message recived資訊，並且寫了6次（log file parallel write）。你可能會問，到底多久，會話傳送一次寫請求？需要知道的是，傳送寫請求前，會話會去持有redo write latch，然後檢查lgwr是不是已經在處理寫請求了，如果已經在寫了，那麼不重複向LGWR傳送請求了，如果沒在寫，才會發，因此如果你的磁碟寫的速率足夠快，那麼lgwr就會被post的次數越多，成正比的關係。還有如果你的cpu足夠強，那麼PL/SQL塊loop的時間就足夠小，時間小了，那麼lgwr被post的次數也就少了，成反比的關係（在磁碟寫速率一定的情況下）。
     最後提醒一句：雖然PL/SQL只有在結束的時候才會等待lgwr寫完成，產生log file sync等待，但是不要認為，在PL/SQL執行過程中，例項crash掉，此次PL/SQL處理的所有事務就會丟失，不是這樣的，只是丟失部分，是pl/sql在執行過程中，會話是傳送寫請求給lgwr的（message sent），lgwr接收到寫請求後，就要寫日誌，只要是被寫進了日誌檔案的事務就是可恢復的。也就是說，雖然前臺沒有等待log file sync，但是後臺其實一直是在忙著處理你的事務日誌的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22034023/viewspace-734926/，如需轉載，請註明出處，否則將追究法律責任。