Multiple Oracle Instances — is this a good practice?

Question

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?

Answer 1

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

Answer 2

Running multiple instances on the same server is possible and in some cases necessary. The oratab is designed specifically to handle managing multiple instances. In cases where you have multiple independent applications it may be preferable to have an instance for each application.

Having sufficient memory is critical. Oracle builds the SGA in shared memory. If you don't have sufficient memory the SGA or processes will start swapping in and out. This is known as thrashing, and results in a significant performance penalty.

It may be possible to tune the instances to run with a smaller SGA. Oracle provides tools to help tune the SGA size. If any of the instances have an excessively large SGA it will penalize all instances.

Another use for memory which is important is buffer space. This acts as secondary cache, and can eliminate significant read I/O.

Running sar (assuming a Unix bases O/S) can give you good diagnostics on where the problem is. Likely problems are either swapping or disk I/O saturation. Adding RAM will likely address either of these problems.

Disk I/O saturation may also be dealt with by moving some tablespaces to other disks. I generally configure Oracle so that I/O is distributed across as many disks as possible.

EDIT: These are a few cases which may require separate instances.

• It is always necessary if you run different versions of Oracle on the same server. Applications are not always certified or capable working with the latest version.
• You may want an instances that can be automatically rolled back to a point in time. (Although I discourage it, I have used this for an automated testing environment.)
• If you have hard-coded schema reference in the applications, you may need different instances to deal with namespace collisions.
• Security requirements may be easier to deal with using separate instances. It may be appropriate to use different servers in this case.
• Running different versions of the same database (development, testing, user acceptance, training, and production) on the same server is safer with separate instances. I would not run all of these environments on the same server, but often run two or more on the same server.

Answer 3

The most critical resource is RAM.

Each running Oracle instance allocates some RAM for its own, when just started and not under load.

We are running a 10g with 10 and 11g with 8 instances, but these are development servers. After restart of the OS some of the Oracle services don't start automatically and must be started manually: Oradim -startup -sid xxx.

We are just beginning to use Automatic Memory Management, but the situation keeps different from SQL Server, where you can add databases as much as the disk space gives.

I your case, with more instances on one machine the SGA for each instance becomes smaller, less precompiled sql can be cached and the machine has to do more sql compilation, which reduces performance.

Adding RAM might help in your situation.