log file sync等待事件分析

AWR数据库版本 ：

Log file sync:等待事件严重

可以发现pin ,flush所花时间非常久

可以发现log file parallel write平均等待2MS

解决办法设置为false

setting "_use_adaptive_log_file_sync" = FALSE

参考文档：1541136.1

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.

ALTER SYSTEM SET "_use_adaptive_log_file_sync"= <FALSE/TRUE>  scope=<both/spfile/memory>;

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:

*** 2012-10-02 08:15:47.049
kcrfw_update_adaptive_sync_mode: post->poll long#=316 sync#=1719 sync=247657
poll=12382 rw=6191 rw+=13890 ack=0 min_sleep=10023


*** 2012-10-02 08:15:47.050
Log file sync switching to polling
Current scheduling delay is 234 usec

 
Switch to post/wait:

***2012-10-02 08:16:23.325
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

*** 2012-10-02 08:16:23.325
Log file sync switching to post/wait
Current approximate redo synch write rate is 758 per sec 

 
Statistics on polling are stored in v$sysstat:

SQL> select name,value from v$sysstat where name in ('redo synch poll writes','redo synch polls');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo synch poll writes                                                    0
redo synch polls                                                          0

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.

 

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

转载于:http://blog.itpub.net/31134212/viewspace-2098125/