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
--
//www.freelists.org/webpage/oracle-l
