log file sync

hi,

I am trying to uncover the root cause of log file sync waits causing an application to decrease in performance. 10046 trace shows hundreds of thousands of lines of log file sync with ela time around 1000. 1ms by itself isn't so bad but throughout the duration of the process it becomes a serious problem.

According to the developer he has removed commits from the loops within the application so that one large commit is issued near the end of each process batch.

I'm having a hard time identifying if the developer has in fact removed the commits OR if there are commits somewhere else within the application that he is unaware of and there are still many commits being issued which are causing the log file sync waits to appear in 10046 tracefiles.

I have used various tools to analyze the traces and all of the tools show roughly 51% of the time spent in log file sync waits and the remaining time in CPU and other waits.

Can anyone share their advice how to identify what is causing these waits to occur? I did raise log_buffer from 372KB to 5MB as a test but that yielded no benefits. The unix admins do not find any problems with the disk array and I do not find any waits to write to the datafiles themselves. The tools show a large amount of time in redo write waits but the redo data files are not being turned over rapidly (once every 10 minutes for a 300MB file) so i'm puzzled where to look next. I copied one redo data file to the same mount as a dummy file using "time" and it only took 5 seconds to copy the file; I know oracle will probably use a different method to manipulate the file but even with this unscientific test I can't see how the waits are associated with the throughput to the disks.

So with that said is there a way to identify in the tracefiles that the commits are being issued? Or any other places to look? There are over 30 cursors involved, typically the cursor with the log file sync is #13 which has a rather lengthy INSERT statement.

Thanks

--
http://www.freelists.org/webpage/oracle-l