High Log File Sync Waits
fuzzydbaMay 19 2009 — edited May 20 2009Hi All,
My PROD database was showing high 'LOG File Sync Waits' as the TOP wait event. The log_buffer was 32k
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- -------- -------------------
log file sync 14,224 9,838 26.85
CPU time 7,627 20.82
log buffer space 15,106 7,006 19.12
enqueue 1,244 3,589 9.80
buffer busy waits 266,018 3,252 8.88
My DBA team (including myself) then changed the log_buffer = 4M and the 'Lof File Sync' wait event shot up to 48% as indicated below;
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- -------- -----------
log file sync 20,406 16,487 48.12
CPU time 7,071 20.64
db file sequential read 19,735,576 3,037 8.86
log file parallel write 8,324 1,888 5.51
async disk IO 485 1,491 4.35
-------------------------------------------------------------
We then reduced the size of the log_buffer from 4M to 1M and we are still getting high 'Log FILE SYNC' Wait events.
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- -------- --------------
log file sync 18,158 13,345 50.02
CPU time 5 ,847 21.91
log buffer space 3,374 3,295 12.35
log file parallel write 9,827 1,553 5.82
db file sequential read 9,112,647 759 2.84
I would greatly appreciate what size should i be setting the log_buffer size too?