Shulou(Shulou.com)05/31 Report--

This article mainly introduces "Oracle adaptive log file sync impact analysis". In daily operation, I believe many people have doubts about Oracle adaptive log file sync impact analysis. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "Oracle adaptive log file sync impact analysis". Next, please follow the editor to study!

The most attractive thing about Oracle is that some answers are hidden in various phenomena, complicated and hazy, just like a detective handling a case, first of all, there must be ideas, secondly, there must be evidence, and then there is solid basic knowledge, and in addition, some luck.

For example, we recently came across a case in which a set of 3-node 11.2.0.4 RAC, an application only uses node 1 (FAILOVER other nodes). The following is the system load. For a certain period of time, the TPS is 554.5.

For each switch between wait/post and polling, it will be recorded in the trace file of LGWR with a time stamp, as follows: switch to polling

* 2018-01-05 08MU 21V 00.000

Kcrfw_update_adaptive_sync_mode: post- > poll long#=316 sync#=1719 sync=247657

Poll=12382 rw=6191 rw+=13890 ack=0 min_sleep=10023

* 2018-01-05 08VOV 21V 00.001

Log file sync switching to polling

Current scheduling delay is 234 usec

Switch to post/wait as follows

* 2018-01-05 08Fran 22purl 00.000

Kcrfw_update_adaptive_sync_mode: poll- > post current_sched_delay=0

Switch_sched_delay=234 current_sync_count_delta=2275

Switch_sync_count_delta=1719

* 2018-01-05 08Fran 22purl 00.000

Log file sync switching to post/wait

Current approximate redo synch write rate is 758 per sec

For the case in this paper, according to the statistics from trace, it is found that there is an obvious time period in log file sync, and the polling mechanism is used. even if there is a record of post/wait, after about 1 minute, it will be switched to polling mechanism.

If you need to disable log file sync adaptation, you can set the hidden parameter _ use_adaptive_log_file_sync to false, which is introduced from the 11gR2 version to control the log file sync adaptive switching function. In versions 11.2.0.1 and 11.2.0.2, the default value is false, and the default value has been changed to true since 11.2.0.3. This is a dynamic parameter that can be changed at the system level

ALTER SYSTEM SET "_ use_adaptive_log_file_sync" = scope=

For this case, the first thing that can be adjusted is to turn off log file sync adaptation, and according to Xiao y, this parameter is also recommended to be turned off by default under 11g for busier OLTP systems.

According to MOS, this log file sync adaptation is supposed to improve the overall performance of log file synchronization. But at the same time, some bug will be generated for this new feature. MOS suggests either upgrading to 11.2.0.4 bug 12.1 or patch, but it is not recommended to turn it off by default for OLTP. After all, as a new feature of 11g, officials suggest to turn it off, isn't it a slap in the face? The following is the adaptive log file sync, known and resolved bug

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

Document 25178179.8 Bug 25178179-Several sessions wait on 'log file sync' in a RAC environment

At this point, the study of "Oracle adaptive log file sync impact analysis" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.