RE: two databases in a server
- From: Carel-Jan Engel <cjpengel.dbalert@xxxxxxxxx>
- To: ric.van.dyke@xxxxxxxxxx
- Date: Thu, 30 Mar 2006 21:22:42 +0200
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@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Tom Fox
> Sent: Thursday, March 30, 2006 12:53 PM
> To: David Sharples
> Cc: roger_xu@xxxxxxxxxxx; Oracle-L@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
> >
> --
> //www.freelists.org/webpage/oracle-l
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
Other related posts:
