Slow OLTP on 14CPU SunFire

[pando]

When you do CTAS do you check what are your users waiting for?

Log file sync is cause by commits, if your application must do vast amount of commits then the way to improve would be putting your redo logs on fast disks. Since you are suffering

log file sync
log file parallel write
log file switch completion
log file sequential read

You might as well check your redo log speed (all members and all groups), you should check you arch process as well, the arch destination must be fast.

I suspect the disks where the redo logs are located are not fast enough.

Also I see you suffer 5 log file switch completion in 10 minutes. When the logfile are switching and the switch is not complete all the sessions are hanged therefore you see the 10ms operations take 5000ms.

Dont you see messages such as, cannot allocate new log in your alert?

And I didnt mean you are Junior, I just thought that one would check first what are the users waiting for when the system hangs! And that info is certainly not in v$session!

Cheers