oralce中_use_adaptive_log_file_sync参数的使用建议

_use_adaptive_log_file_sync该参数在commit次数较多的系统中,post/wait and polling特性对性能影响明显,主要是在写日志上的等待上影响了事务的提交速度.建议在11g中关闭(设置为false).

该参数可以参考以下解述:

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

There are five more hidden parameters with that string

select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx and a.ksppinm like ‘_%adaptive_log%’ escape ‘’
order by name;

sys@ANBOB>/

NAME VALUE DESCRIPTION
————————————————– ———- ———————————————————————-
_adaptive_log_file_sync_high_switch_freq_threshold 3 Threshold for frequent log file sync mode switches (per minute)
_adaptive_log_file_sync_poll_aggressiveness 0 Polling interval selection bias (conservative=0, aggressive=100)
_adaptive_log_file_sync_sched_delay_window 60 Window (in seconds) for measuring average scheduling delay
_adaptive_log_file_sync_use_polling_threshold 200 Ratio of redo synch time to expected poll time as a percentage
_adaptive_log_file_sync_use_postwait_threshold 50 Percentage of foreground load from when post/wait was last used
_use_adaptive_log_file_sync TRUE Adaptively switch between post/wait and polling

So how do you know if you’re using this feature? The most obvious sign will be in the LGWR trace file.
There will be messages each time that a switch happens, looking something like this:
in the case my db name and instance name both is kaoshi .

[oracle@anbob trace]$ pwd
/oracle/diag/rdbms/kaoshi/kaoshi/trace

[oracle@anbob trace]$ grep -n -B 4 “kcrfw_update_adaptive_sync_mode” *_lgwr_*

Adaptive Switching Between Log Write Methods can Cause ‘log file sync’ Waits (文档 ID 1462942.1)
—
kaoshi_lgwr_3823.trc-147-*** 2013-11-23 12:06:23.288
kaoshi_lgwr_3823.trc-148-Log file sync switching to polling
kaoshi_lgwr_3823.trc-149-Current scheduling delay is 1 usec
kaoshi_lgwr_3823.trc-150-Current approximate redo synch write rate is 30 per sec
kaoshi_lgwr_3823.trc:151:kcrfw_update_adaptive_sync_mode: poll->post current_sched_delay=0 switch_sched_delay=1 current_sync_count_delta=0 switch_sync_count_delta=90
kaoshi_lgwr_3823.trc-152-
kaoshi_lgwr_3823.trc-153-*** 2013-11-23 12:56:23.567
kaoshi_lgwr_3823.trc-154-Log file sync switching to post/wait
kaoshi_lgwr_3823.trc-155-Current approximate redo synch write rate is 0 per sec
kaoshi_lgwr_3823.trc:156:kcrfw_update_adaptive_sync_mode: post->poll long#=36 sync#=131 sync=390 poll=1058 rw=217 ack=0 min_sleep=1058
—
kaoshi_lgwr_3823.trc-160-Current scheduling delay is 1 usec
kaoshi_lgwr_3823.trc-161-Current approximate redo synch write rate is 43 per sec
kaoshi_lgwr_3823.trc-162-
kaoshi_lgwr_3823.trc-163-*** 2013-11-23 14:37:46.978
kaoshi_lgwr_3823.trc:164:kcrfw_update_adaptive_sync_mode: poll->post current_sched_delay=0 switch_sched_delay=1 current_sync_count_delta=25 switch_sync_count_delta=131
kaoshi_lgwr_3823.trc-165-
kaoshi_lgwr_3823.trc-166-*** 2013-11-23 14:37:46.978
kaoshi_lgwr_3823.trc-167-Log file sync switching to post/wait
kaoshi_lgwr_3823.trc-168-Current approximate redo synch write rate is 8 per sec
kaoshi_lgwr_3823.trc:169:kcrfw_update_adaptive_sync_mode: post->poll long#=86 sync#=121 sync=2 poll=1058 rw=152 ack=0 min_sleep=1058
Disable adaptive log file sync

If there is a need to disable adaptive log file sync this can be done by setting _use_adaptive_log_file_sync = false either in the spfile and restarting the database or dynamically in memory.

“_use_adaptive_log_file_sync” is a dynamic parameter that can be changed at system level.

ALTER SYSTEM SET “_use_adaptive_log_file_sync”= false;
Known Issues with “_use_adaptive_log_file_sync” set to TRUE

See the following documents with information on bugs involving Adaptive Log File Sync:
Document 1462942.1 Adaptive Switching Between Log Write Methods can Cause ‘log file sync’ Waits
Document 13707904.8 Bug 13707904 – LGWR sometimes uses polling, sometimes post/wait
Document 13074706.8 Bug 13074706 – Long “log file sync” waits in RAC not correlated with slow writes

References
Waits for “log file sync” with Adaptive Polling vs Post/Wait Choice Enabled (文档 ID 1541136.1)