_use_adaptive_log_file_sync设置 降低log_file_sync等待

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

_use_adaptive_log_file_sync造成性能下降的原因可能是其导致LGWR使用了polling 方式来取代 post/wait，并且polling的间隔是10ms，这个间隔是在代码里写死的。

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

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%'

order by name;

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

6 rows selected.

[oracle@cnbjsjh01poraclekcptl01 trace]$ pwd

/u01/oracle/diag/rdbms/racdb/RACDB1/trace

[oracle@cnbjsjh01poraclekcptl01 trace]$ grep -n -B 4 "kcrfw_update_adaptive_sync_mode" *_lgwr_*

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

--

RACDB1_lgwr_27890.trc-6255-Warning: log write elapsed time 523ms, size 8371KB

RACDB1_lgwr_27890.trc-6256-

RACDB1_lgwr_27890.trc-6257-*** 2016-05-11 18:30:08.505

RACDB1_lgwr_27890.trc-6258-Warning: log write elapsed time 502ms, size 11299KB

RACDB1_lgwr_27890.trc:6259:kcrfw_update_adaptive_sync_mode: poll->post current_sched_delay=0 switch_sched_delay=1 current_sync_count_delta=2 switch_sync_count_delta=7

--

RACDB1_lgwr_27890.trc-6296-Warning: log write elapsed time 545ms, size 2KB

RACDB1_lgwr_27890.trc-6297-

RACDB1_lgwr_27890.trc-6298-*** 2016-05-12 01:30:23.549

RACDB1_lgwr_27890.trc-6299-Warning: log write elapsed time 532ms, size 11870KB

RACDB1_lgwr_27890.trc:6300:kcrfw_update_adaptive_sync_mode: post->poll long#=2 sync#=7 sync=407 poll=1961 rw=616 ack=0 min_sleep=1961

--

RACDB1_lgwr_27890.trc-6563-Warning: log write elapsed time 514ms, size 10825KB

RACDB1_lgwr_27890.trc-6564-

RACDB1_lgwr_27890.trc-6565-*** 2016-05-12 21:54:40.869

RACDB1_lgwr_27890.trc-6566-Warning: log write elapsed time 504ms, size 5329KB

RACDB1_lgwr_27890.trc:6567:kcrfw_update_adaptive_sync_mode: poll->post current_sched_delay=0 switch_sched_delay=1 current_sync_count_delta=1 switch_sync_count_delta=7

--

RACDB1_lgwr_27890.trc-6664-Warning: log write elapsed time 514ms, size 3984KB

RACDB1_lgwr_27890.trc-6665-

RACDB1_lgwr_27890.trc-6666-*** 2016-05-13 11:01:52.650

RACDB1_lgwr_27890.trc-6667-Warning: log write elapsed time 751ms, size 1027KB

RACDB1_lgwr_27890.trc:6668:kcrfw_update_adaptive_sync_mode: post->poll long#=1 sync#=7 sync=436 poll=2276 rw=1138 ack=0 min_sleep=1961

修改：

“_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)
maclean和玉面飞龙等大神的文章

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28572479/viewspace-2130627/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28572479/viewspace-2130627/