The parameter _use_adaptive_log_file_sync was introduced in 11gR2 and controls whether adaptive switching between post/wait and polling is enabled.
DEFAULT VALUE of this parameter is TRUE. i.e according to oracle algorithm and internal statistics, the adaptive switching will happen between post/wait and polling method.
There are 2 methods by which LGWR and foreground processes can communicate in order to acknowledge that a commit has completed:
Post/wait: traditional method available in previous Oracle releases LGWR explicitly posts all processes waiting for the commit to complete.
Polling: Foreground processes sleep and poll to see if the commit is complete.
Below is the snippet from lgwr trace, which shows adaptive switching between these methods.
*** 2017-11-20 15:04:58.691 Warning: log write elapsed time 965ms, size 3KB kcrfw_alfs_update_mode: post->poll long#=53 sync#=313 sync=4833 poll=4744 rw=1923 ack=2372 min_sleep=1069 *** 2017-11-21 15:37:44.019 Log file sync switching to polling --- >>>> It shows current method is polling Current scheduling delay is 1 usec Current approximate redo synch write rate is 104 per sec *** 2017-11-21 15:53:09.673 Warning: log write elapsed time 2165ms, size 2KB kcrfw_alfs_update_mode: poll->post curr_sched_delay=0 switch_sched_delay=1 curr_sync_count_delta=21 switch_sync_count_delta=313 *** 2017-11-21 16:05:16.014 Log file sync switching to post/wait -- >>>> It shows current method is post/wait Current approximate redo synch write rate is 7 per sec
However, there are few cases, where this adaptive switching caused the lgwr process to block other session or causes huge log file sync waits.
In that case, if you wish to disable this feature, then
alter system set "_use_adaptive_log_file_sync"=FALSE scope=both;
NOTE – Please check with oracle support before doing this change in the production database.
You can check the redo polling stats from the database using below query.
1* select name,value from v$sysstat where name in ('redo synch poll writes','redo synch polls'); NAME VALUE ---------------------------------------------------------------- ---------- redo synch poll writes 561500 redo synch polls 753849