Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

High Log File Sync Waits

fuzzydbaMay 19 2009 — edited May 20 2009
Hi 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?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 17 2009
Added on May 19 2009
4 comments
2,296 views