Disclaimer: Much of what follows is pure speculation on my part. It could be completely wrong, and I'm putting it out there in the hopes that it'll eventually be proven one way or the other.
"LGWR is unable to post the processes fast enough, due to excessive commits. It is quite possible that there is no starvation for CPU or memory, and that I/O performance is decent enough. Still, if there are excessive commits, LGWR has to perform many writes/semctl calls, and this can increase ‘log file sync’ waits. This can also result in sharp increase in redo wastage statistics."Maybe Oracle is reading Riyaj's blog? It appears that they came up with a new algorithm where LGWR doesn't post. My guess: foreground processes can probably still post LGWR - but LGWR never posts back. Instead, foreground processes in WAIT mode "poll" either a memory structure or LGWR itself. It could be an in-house implementation by Oracle, it could still use semaphores, or it could use message queues somehow [seems like a long shot but the unix poll() call is found there]. There's one interesting challenge that I can think of in implementing this. With the semaphore approach, all the active commits are *sleeping* (off the CPU) while LGWR flushes the log buffer. There could be dozens or hundreds of foreground processes simultaneously commiting on a very busy system. If we switch to a polling method, how do we ensure that these hundreds of processes don't start spinning and steal CPU from LGWR, making the whole system even worse than it was in the beginning? The answer might lie in a quick search for "adaptive_log_file_sync" from the underscore parameters. 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
| Name | Default | 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 |
It appears that there are a number of knobs to turn with this new algorithm - and it looks like Oracle is somehow dynamically calculating a "polling interval". Furthermore, it seems to be taking "scheduling delay" into account. It also seems that by default, we're limited to switching modes every 20 seconds (3 per minute). This switching seems to be controlled by "thresholds" - and the threshold for enabling the new polling mode seems to be based on time. From the default percentage, it looks to me like Oracle won't switch until it thinks the poll time will be less than half of the current post time. For switching back it also seems to be another "half" percentage (50), though I'm not sure what "foreground load" might mean. Now there's one other underscore parameter which I noticed while looking for things related to log file sync:
| Name | Default | Description |
|---|---|---|
| _fg_sync_sleep_usecs | 0 | Log file sync via usleep |
This parameter is interesting for one reason: The adaptive sync parameters were introduced in 11g, but this parameter was introduced in 10g. And if you think about the name, it actually sounds very similar to the "polling" strategy and not like a semaphore strategy at all! Foreground processes call usleep() during a log sync - if you're calling wait() then you don't need to sleep. But if you're polling, you definitely need to sleep. Maybe Oracle has been working on this idea since 10g? I wouldn't put it past them. :) 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:
*** 2012-10-16 01:47:50.289 kcrfw_update_adaptive_sync_mode: poll->post current_sched_delay=0 switch_sched_delay=1 current_sync_count_delta=8 switch_sync_count_delta=59 *** 2012-10-16 01:47:50.289 Log file sync switching to post/wait Current approximate redo synch write rate is 2 per sec *** 2012-10-16 02:51:19.285 kcrfw_update_adaptive_sync_mode: post->poll long#=51 sync#=352 sync=4600 poll=1061 rw=500 rw+=500 ack=7 min_sleep=1061 *** 2012-10-16 02:51:19.285 Log file sync switching to polling Current scheduling delay is 1 usec Current approximate redo synch write rate is 117 per secI mentioned that the underscore parameters were introduced in 11g; however, my current understanding is that only in 11.2.0.3 did the default value of _use_adaptive_log_file_sync change to true!! Is it comforting that they waited so long, or is it scary that they'd make this change with no available documentation or troubleshooting information? I guess it depends on your perspective. Here's mine:
Regardless, it's now been 12 months since 11.2.0.3 was released. At present, it's only mentioned in a single support article. This offers at least some evidence that adaptive log file sync hasn't caused widespread panic. For the past year, many of you have been using this feature without even knowing - and there weren't enough problems to merit even two Oracle Support notes. I sent a few emails out to friends who also tend to work on large, loaded systems. I wasn't the first person to stumble across this - James Morle mentioned it in a tweet a few months ago, and (not surprisingly) Riyaj has stumbled across the feature as well. It seems to me that the issues that have been encountered intersect with RAC and/or the switching process itself. But my informal survey was hardly scientific. Overall, I think Oracle dodged the bullet this time. But they still made an important change without supporting documentation. To my friends inside the big red mothership: Please don't do that again! We like it better the other way. Have you ever heard of this new 11.2.0.3 feature? Have you heard of the underscore parameter? I'd love to hear your stories!
Ready to optimize your Oracle Database for the future?