Re: Num of instances

In article <C7AoDE.CIr_at_melpar>, yenzi_at_melpar (Damian Yenzi) writes:
|>
|> I have a question concerning performance that is related to the number of
|> oracle instances that are running on a platform. For example, I have 5
|> applications (systems) such as accounting, manufacturing, etc that have been
|> implemented in oracle. Each application is comprised of at least 30 tables.
|> The total database is over 5 gigabytes. The machine is a sequent running unix
|> and has 8 CPUs. We are thinking of switching to 5 databases with 5 oracle
|> instances (each application has its own instance/database/CPU). Since oracle
|> is architected to run on a machine with multi- processors, this should increase
|> performance. Has anyone actually done this?
|> What were the problems that were encountered? What are some advantages and
|> disadvantages of this technique. Thank you for your time.
|>
|> Damian Yenzi (uunet!melpar!yenzi)
|> E-Systems
|> Dba

I previously held a position as DBA for a system where we did exactly as you suggest, for three reasons:

(1) Reliability - If the system tablespace crashes, only one instance is out of commission during recovery; the other instances can go on.

(2) Ease of reconfiguration - We could move individual instances to other machines without having to rebuild the database.

(3) Seperation of log file traffic - In our case, only 1 instance had much log file traffic; the others were mainly read-only with infrequent updates, and hence very few log files. If we had used one instance for everything, then recovering one of the read-only tablespaces would still require wading through hundreds of log files.

The only disadvantage we encountered was the case where a program needed data from more than one instance. We wrote our application to connect to as many instances as needed. (For anything not written in C, we used database links to accomplish the same thing.)

I cannot address the performance question, as we instituted the multi-instance configuration early in the project and have no basis for comparison.

Hope this helps

Glenn Goodrum
Sierra Geophysics
ggoodrum_at_sierra.com

Disclaimer: These are my own opinions from my own experience and have nothing to do with my current employer. Received on Mon May 24 1993 - 21:52:48 CEST