Change in LGWR behavior , living with beast or best?

If you are experiencing the log file sync wait after an upgrade to 11.2.0.3 and most importantly that as there are no other symptoms of issues with I/O or in other areas, the problem could be with excessive switching between post/wait and polling wait methods.

While writing previous post, came across the another interesting note and reference post that there is a change in the LGWR behavior appears from 11.2.0.3,

Background:- LGWR posts/poll to the user session (acknowledgement) that receives requests or send acknowledgement to the foreground process or to other process like checkpoint.

But  due to kernel scheduling delays (due to CPU starvation, memory starvation) LGWR can also be victim waiting to post/poll or another angle could be user sessions may not receive the acknowledgement in time which can drastically increase the log file sync events.

According to metalink note, Adaptive Switching Between Log Write Methods can Cause 'log file sync' Waits [ID 1462942.1] , On a system with 11.2.0.3 (solaris) log file sync can be appear , even if the other contributors (read here) are taking less wait time which revealing in your troubleshooting.

This is because of change in the lgwr post or polling (kernel calls to post to foreground processes) behavior with an underscore parameter, called _use_adaptive_log_file_sync (default true)

Description of _use_adaptive_log_file_sync

With " _use_adaptive_log_file_sync " = true:
Adaptive switching between post/wait and polling for log file sync is implemented.
When polling is adaptively selected, the polling interval is also dynamically adjusted.
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.

If you dont want to live with beast, then Set the parameter _use_adaptive_log_file_sync = false and restart the database:

SQL> ALTER SYSTEM SET "_use_adaptive_log_file_sync" = FALSE;

As this parameter by default true, you may need to consider to set it false if you are seeing higher log file sync waits

Along with this there are other underscore parameters for lgwr behavior which were discussed in the reference link below. Thanks to Pythian Team and its blog.

References:-

http://www.pythian.com/news/36791/adaptive-log-file-sync-oracle-please-dont-do-that-again/

Adaptive Switching Between Log Write Methods can Cause 'log file sync' Waits [ID 1462942.1]

-Thanks

Geek DBA