-
Title
Post slow log file sync wait is very high on 11g -
Description
Post is very slow and AWR report shows log file sync wait is over 40% of DB time as the Top wait event.
Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Tota Wait % DB
Event Waits Time Avg(ms) time Wait Class
------------------------------ ------------ ---- ------- ------ ----------
log file sync 14,708,268 238. 16 44.7 Commit
DB CPU 227. 42.6
db file sequential read 3,052,025 10.2 3 1.9 User I/O
db file scattered read 1,505,112 5013 3 .9 User I/O
direct path read 992,417 3726 4 .7 User I/O
direct path write temp 527,974 1401 3 .3 User I/O
direct path read temp 650,772 913. 1 .2 User I/O
SQL*Net more data to client 10,739,271 900. 0 .2 Network
control file sequential read 1,541,590 782. 1 .1 System I/O
enq: RO - fast object reuse 30,126 436. 14 .1 Application -
Cause
Oracle issue related to adapative switching between log write methods can cause 'log file sync' waits, see metalink doc 1462942.1
_use_adaptive_log_file_sync is set to TRUE by default in 11.2.0.3+ versions
-
Resolution
WORKAROUND:
1. Set the parameter _use_adaptive_log_file_sync = false and restart the database:
SQL> ALTER SYSTEM SET "_use_adaptive_log_file_sync" = FALSE;
2. In cases where a restart is not feasible, then you can set in memory and also in the SP file for when a restart does occur:
SQL> ALTER SYSTEM SET "_use_adaptive_log_file_sync"=FALSE; - to set in memory
SQL> ALTER SYSTEM SET "_use_adaptive_log_file_sync"=FALSE scope=sfile sid='*'; - to set on spfile, so parameter is used at next restart.