If I see redo logs are causing log sync wait event on the application processes, what could be the cause?

Too often commits, too small log buffer (1M at present on solaris 2.6), some resource limit? How does one figure if 1M log buffer is sufficient (its filling up in less than 3sec).

what are some queries I can run to find what should be minimum log buffer size. Also, having data files, etc on the redo log drive - would this seriously impact performance of the LGWR and consequently application?

Thanks very much in advance.

log sync waits are most commonly caused by frequent commits. When you commit, you must wait for LGWR to flush the contents of the redo log buffer to disk (else we could not assure you of recovery).

A small log buffer would manifest itself as a LOG_BUFFER_SPACE wait.

Having data files and other things on the same device as your online redo log can definitely impact log writers ability to do its job as fast as possible.

Things you can do:

o look at your transactions. If you are "over committing" -- commiting in the mistaken belief that you are saving resources and not on true transactional
boundaries -- stop it. Commit only when your transaction is over and do not commit too frequently (if you have very small transactions, so be it -- you MUST commit but if you are committing just for the sake of committing, don't)

o speed up LGWR. Make it more efficient. Ensure you are not using something like RAID 5 for log devices. Reduce contention on that device (let LGWR own it). Ensure LGWR and ARCH are not contending with eachother (you want at lead 5 devices dedicated to logging -- you NEED to mirror redo members and you NEED to archive. So, if you have disks 1..5 you can:

have redo log groups 1, 3, 5, 7, .... on disks 1 and 3
redo log groups 2, 4, 6, 8, .... on disks 2 and 4
archive destination to disk 5

Now, when LGWR is writing to groups 1, 3, 5, 7, .... on disks 1/3, ARCH will be reading groups 2, 4, 6, 8, ... on disks 2/4 and writing to disk 5. When LGWR advances, so does ARCH and they'll switch disks. Never does ARCH and LGWR contend with eachother.

References