Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: two databases in a server

RE: two databases in a server

From: Carel-Jan Engel <cjpengel.dbalert_at_xs4all.nl>
Date: Thu, 30 Mar 2006 21:22:42 +0200
Message-Id: <1143746562.20693.23.camel@dbalert199.dbalert.nl>


Ric, it depends, as always. Technically you are right, in many cases one can put the instances together. For day to day systems management many times it is not the preferable way to go.

Last year one of my customers ran into huge problems, when their nicely designed application, provided to appr. 70 companies and hosted on their server in one database/1 instance (8i), hit the ceiling. After adding a little load it just stopped performing. There were 70 schema's, all with merely the same set of tables, all executing nice statements, using bind variables and all. They did read most of Tom Kyte's stuff, and were using the database properly with their home-grown application. However, when one of the statements got executed again (soft-parsed, cursors were not held in this web application, you can't have it all) the statement hash was detected, the instance thought 'I have a plan for it' and it picked the right cursor. A cursor with many, many children, one for each schema. When looking for the right child, it hold a latch. Then the OS kicked the active latch holding porcess from the CPU, and 1800 other processes were allowed to try to get hold of the latch. This failed. The first thing I did wen they called me was to split the data in two databases, and run the whole thing in two instances. On the same server. Halving the number of child cursors got rid of most of the latch contention, and bought us enough time to set up a new set of servers, and now we run the app on two smallish servers in 4 databases with 1 instance each, no fancy RAC ASM stuff, just Standard Edition, cheap and fairly easy to manage.

Another example is the big organization with > 100 databases, using 1 server/database. Every new project got their own database, and their own server. Now they want to do some consolidation. Putting apps together in databases is a real headache: different services, different service windows, every app owner has his own kingdom. Apart from that: technical issues. Central schema's with 'standard reusable code', sometimes customized, makes it almost imppossible just to join to applications in one database, if not all the central code is walked through. Customizations need to be set apart by different naming, or code need to be changed to call 'standard functions' from 'non-standard' schema's. We are planning just to put the instances together, and capsulate the resources every instance gets by means of WorkLoad Manager (this is AIX). When you are in the luxury position of setting up new systems, apps, whatever, and know for sure one app will not insist on installing 10.3 three months before the other app will be certified for it, you might consider putting them together in one instance. Otherwise, just leave them apart, and save yourself the hassle of splitting them again sooner or later. (Although it might get easier and easier using datapump/transportable tablespaces. But enforce independency if you want to be able to do that, and assert now you will get the maintenance window for it when needed).

Just my $0.02

Best regards,

Carel-Jan Engel

===
If you think education is expensive, try ignorance. (Derek Bok) ===

On Thu, 2006-03-30 at 12:04 -0600, Ric Van Dyke wrote:

> Generally it's bad because there is a certain level of resource
> consumption for an instance of any size. So pay the price ONCE and have
> an instance large enough to do all the work needed by all the
> applications using the database. There can be cases where multiple
> instances on the same box are needed, but many cases that I have seen it
> was not needed. Many times folks can use one instance (or at least
> fewer instances) to get the work done.
>
> Ric Van Dyke
> Hotsos Enterprises
> -----------------------
> Hotsos Symposium, be there:
> http://www.hotsos.com/portal/events/SYM06
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tom Fox
> Sent: Thursday, March 30, 2006 12:53 PM
> To: David Sharples
> Cc: roger_xu_at_dp7uptx.com; Oracle-L_at_Freelists. Org (E-mail)
> Subject: Re: two databases in a server
>
> I've heard the argument before about having more than one instance on a
> machine is a bad idea.
>
>
> However, on the flip side, having 50 applications share one instance
> can
>
>
> be bad, as if one app needs a particular patch, you're affecting 49
> other
> applications due to one application's requirements.
>
> Anyone care to elaborate why more than one instance is bad?
>
> --Tom
>
> On Thu, 30 Mar 2006, David Sharples wrote:
>
> > by joining them into a single instance - that is best practise
> >
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 30 2006 - 13:22:42 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US