11.2之前，oracle的lgwr寫入模式為post/wait 11.2之後新增了polling模式，可以與post/wait模式自動切換 通過隱藏參數 _use_adaptive_log_file_sync 參數來控制 查看該隱藏參數的方法： 當參數設置為false時，lgwr還是採用post/ ...
11.2之前，oracle的lgwr寫入模式為post/wait
11.2之後新增了polling模式，可以與post/wait模式自動切換
通過隱藏參數 _use_adaptive_log_file_sync 參數來控制
查看該隱藏參數的方法：
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM SYS.x$ksppi x, SYS.x$ksppcv y WHERE x.indx = y.indx AND x.ksppinm LIKE '%_use_adaptive_log_file_sync%';
當參數設置為false時，lgwr還是採用post/wait方式將日誌從buffer寫入磁碟
當參數設置為true是，lgwr寫入方式會自動在post/wait和polling模式之間進行切換，可能會造成比較嚴重的log file sync （當使用polling模式時）
建議關閉此參數：
alter system set "_use_adaptive_log_file_sync"=FALSE;
模式切換時，lgwr的trace中會記錄類似如下的信息：
RACDB1_lgwr_27890.trc-6094-Warning: log write elapsed time 649ms, size 11252KB RACDB1_lgwr_27890.trc-6095- RACDB1_lgwr_27890.trc-6096-*** 2016-05-10 13:28:41.481 RACDB1_lgwr_27890.trc-6097-Warning: log write elapsed time 654ms, size 16877KB RACDB1_lgwr_27890.trc:6098:kcrfw_update_adaptive_sync_mode: post->poll long#=2 sync#=9 sync=1031 poll=1961 rw=574 ack=0 min_sleep=1961 -- RACDB1_lgwr_27890.trc-6115-Warning: log write elapsed time 532ms, size 16479KB RACDB1_lgwr_27890.trc-6116- RACDB1_lgwr_27890.trc-6117-*** 2016-05-10 18:22:14.255 RACDB1_lgwr_27890.trc-6118-Warning: log write elapsed time 781ms, size 316KB RACDB1_lgwr_27890.trc:6119:kcrfw_update_adaptive_sync_mode: poll->post current_sched_delay=0 switch_sched_delay=1 current_sync_count_delta=2 switch_sync_count_delta=9 -- RACDB1_lgwr_27890.trc-6168-Warning: log write elapsed time 855ms, size 9153KB RACDB1_lgwr_27890.trc-6169- RACDB1_lgwr_27890.trc-6170-*** 2016-05-11 06:29:28.814 RACDB1_lgwr_27890.trc-6171-Warning: log write elapsed time 568ms, size 10345KB RACDB1_lgwr_27890.trc:6172:kcrfw_update_adaptive_sync_mode: post->poll long#=2 sync#=7 sync=964 poll=1961 rw=969 ack=0 min_sleep=1961
兩種模式的理解：（兩種模式主體都是前臺進程，post/wait是等待lgwr通知，polling是主動輪序lgwr）
Post/wait：用戶會話被動等待LGWR通知redo寫入到log file完畢，這種方式響應速度比較快。若cpu空閑時採用這種方式可以體驗到更好的響應時間。
Polling：用戶會話主動輪詢LGWR，觀測是否完成寫入（輪詢的間隔是10ms）。這種方式比Post/wait方式響應速度慢，但LGWR不直接把完成的消息通知到很多用戶會話，可以節約CPU資源。若cpu繁忙時採用這種方式可以降低cpu資源的消耗。
官方對兩種模式的解釋：
Adaptive Log File sync was introduced in 11.2. the feature is exactly enabled since release 11.2.0.3 , It’s enabled through an underscore parameter called _use_adaptive_log_file_sync and the description of this parameter is: adaptively switch between post/wait and polling.
Oracle can switches between the 2 methods:
Post/wait, traditional method for posting completion of writes to redo log
LGWR explicitly posts all processes waiting for the commit to complete.
The advantage of the post/wait method is that sessions should find out almost immediately when the redo has been flushed to disk.
Polling, a new method where the foreground process checks if the LGWR has completed the write.
Foreground processes sleep and poll to see if the commit is complete. The advantage of this new method is to free LGWR from having to inform many processes waiting on commit to complete thereby freeing high CPU usage by the LGWR.If post/wait is selected and the foreground processes fail to receive a post from LGWR, an incident is recorded.diagnostic traces are performed, and polling is used instead of post/wait.
Oracle uses semaphores extensively, If you look for references to “commit” in the Oracle docs, you’ll find the word “post” everywhere when they talk about communication between the foreground processes and LGWR. Now, remember that a COMMIT has two options: first, IMMEDIATE or BATCH and second, WAIT or NOWAIT. It looks like this to me:
Immediate: FG process will post to LGWR, triggering I/O (default)
Batch: FG process will not post LGWR
Wait: LGWR will post FG process when I/O is complete (default)
NoWait: LGWR will not post FG process when I/O is complete
參考：https://blog.csdn.net/rgb_rgb/article/details/72804143
