Adaptive Log File Sync Optimization (文档 ID 1541136.1)

Applies to:

Goal

The document explains adaptive log file syncs introduced in 11.2 and the supporting parameter "_use_adaptive_log_file_sync" .

Solution

'log file sync' Waits

When a user session commits (or rolls back), the redo information needs to be flushed to the redo logfile. The user session will post the LGWR to write all redo required from the log buffer to the redo log file. The user session waits on ‘log file sync’ while waiting for LGWR to post it back to confirm all redo changes are safely on disk.
For further information on 'log file sync' waits and how to alleviate performance problems arising from high waits see:

For a list of known issues in 11.2 see:

Parameter: _use_adaptive_log_file_sync

The parameter “_use_adaptive_log_file_sync” was introduced in 11gR2 and controls whether adaptive switching between post/wait and polling is enabled.
In 11.2.0.1 and 11.2.0.2 the default value for the parameter is false. From 11.2.0.3, the default value has been changed to true.

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

Adpative Log File Sync

There are 2 methods by which LGWR and foreground processes can communicate in order to acknowledge that a commit has completed:

• Post/wait - traditional method available in previous Oracle releases
  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
  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.

Initially the LGWR uses post/wait and according to an internal algorithm evaluates whether polling is better. Under high system load polling may perform better because the post/wait implementation typically does not scale well. If the system load is low, then post/wait performs well and provides better response times than polling. Oracle relies on internal statistics to determine which method should be used.  Because switching between post/wait and polling incurs an overhead, safe guards are in place in order to ensure that switches do not occur too frequently.

All switches are recorded in LGWR's trace file with a time stamp and the string "Log file sync switching to ...":

Switch to polling:

 
Switch to post/wait:

 
Statistics on polling are stored in v$sysstat:

In the above example we see that polling is not occurring

The statistics are also included in the AWR report:

In the above AWR we see that polling is occurring

Known Issues with "_use_adaptive_log_file_sync" set to TRUE

In the vast majority of cases adaptive log file sync improves the overall performance of log file  synchronisation.
Following is a list of known issues.
As these issues are fixed in 11.2.0.4 and 12.1, it is advisable to be at least on one of these versions in order to avoid the known issues.

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. 

References