One of my clients has deployed our product's db into a Solaris machine which already has 3 instances of Oracle. So, right now there are 4 instances of Oracle running on the same machine. And now we're experiencing performance issues.
I have no access to other instances or the machine, and all the tools I have are alert.log, AWR, and ADDM. I know there's something related to multiple instances, but I cannot prove it.
So, my question is, have you experienced a similar situation? How should I deal with it?
How can I identify the cause of performance issues related to multiple instances?
Best Answer
Isaac, preferably we run one instance on one server and implement the different applications as schemas and services in that single datatabase. If the server has enough memory, there won't be a problem, if the applications all behave like good Oracle citizens. As soon as there is an application that does not use bind variables there is a growing pain on the host. The same for applications that keep creating new connections every few seconds, instead of re-using existing connections. Things like auto commit also does not help to improve the situation. What is the normal behavior of your application? Does it do large updates? When does it do a commit? How much redo is generate/hour? Does your app use bind variables? In many cases, the location of the online redolog files is critical. If the app does generate a lot of redo (or competes with an other app that does this), the logwriter will slow down and cause a big performance degradation because the session is waiting for the logfile write to complete. Give you customer a disk layout where the redo's are on dedicated raid-10 disks and see how much this helps. Also find out the memory allocation situation. There might be a need for a fair redistribution of available memory.
I hope this helps