In part 1 of the log file sync case I’m writing about, I did an analyze following Troubleshooting: ‘Log file sync’ Waits (Doc ID 1376916.1)
But I was not able to fully point out the reason for the high average time for log file sync. Writing to the log files was not the main contributor to the wait nor was cpu shortage.
The application do commit very frequent with an average of 5 user calls per commit, which is way below Oracles recommendation of 30 calls per commit.
Craig Shalahammer (orapub) helped me analyzing the issue and he used some of my data to show how you can use R to visualize the wait event. Read it and you get more details about the first picture below. I added the LFPW just so you can see that it correlates with LFS.
In general we are doing good and the question is is this an issue ?
It depends on your users, are they complaining? then it is an issue.
In my case it is an issue since our users do complain about performance, I believe this is related to the combination of an “over-committing” application and fairly slow disks.
At peak times when we have many sessions committing we cannot cope with the load so our users has to wait for LFS(LFPW) since it is a queue building up of sessions that wants to commit. If we could reduce the commit rate from the application the picture would be different. But also if we could improve write performance to disk. Doing both would be very good. We are building a new test environment were we will use fusion IO cards to improve write performance, changing commit rate is not in our control and not an option right now. We haven’t been able to test it yet, but I will come back and write about it when we have done our tests.