Re: Instance with thousands of schemas

In article <Xns902E8627EFCF6audun.jyahoo.no_at_130.133.1.4>,   audun_j_at_yahoo.no (Audun Jensen) wrote:
> "Falco Paul" <falco_at_palm.nl> wrote in <9471a8$p8v$1_at_azure.nl.gxn.net>:
>
> >This would be the best thing to do, Audun, even though Oracle does
> >support multiple instances.
> >I am not aware of any limitiation on the number of schema's you can
 use.
> >Think about tablespace storage before you go ahead.
> >If you want to minimize the impact of a disk failure, spread the
> >schema's among your disks.
> >Further, Oracle has many ways of organising user privileges. The best
> >thing would probably be to use roles.
> >You could also use synonyms in certains cases. There's also the
 Virtual
> >Private Database feature in Oracle EE,
> >but I don't think it will be of much use in your situation.
> >See the docs for more details.
> >
> >Falco Paul
> >Cigma Consult
> >The Netherlands
> >
> >"Audun Jensen" <audun_j_at_yahoo.no> schreef in bericht
> >news:Xns902DA022CCF33audun.jyahoo.no_at_130.133.1.4...
> >> Hi,
> >> I'm starting to migrate a MS SQL Server db to Oracle and have some
> >> questions I would like to have comments on.
> >> Today our SQL Server database consists of more than 3500 user
> >> databases

 and
> >> the number is increasing. Each database corresponds to a user
> >> "project".

 As
> >> some of you probably are aware of, SQL Server have a
> >> "database"-concept that differs from Oracle's, in the way that one
 SQL
> >> Server instance can consist of many databases, each database
> >> consisting of users, tables, views, procs, triggers etc.. I believe
> >> the way to do this using Oracle

 will
> >> be to create and implement a schema for each database, containing
 the
> >> necessary objects and constraints.
> >>
> >> This leads to an instance containing thousands of schemas, each
> >> containing 50-100 tables. In addition I will have to create users
 who
> >> can access one or more of these "databases" depending on how many
> >> "projects" the customer creates.
> >>
> >> Is there anybody who have any experience in this number of schemas?
> >> What will I have to think of in order to have an instance that is
> >> operatable

 and
> >> running smooth?
> >>
> >> TIA
> >>
> >> AJ
> >
> >
> >
>
> What about the tablespaces? Each customer will have one or more
 projects
> (schemas). Should I let each schema have its own tablespace (maybe
 two; one
> for the data and one for the indexes), should I create one tablespace
 for
> each customer or maybe I should let all schemas reside in the same
> tablespace?
> From a management point of view (read minimize management) I guess
> everything should be put in one (or two) tablespaces. From
 performance
> considerations I guess that is no good idea?
>
> What do you think?
>
> AJ
>

AJ,

Given that you always want to build contingency into the size of tablespaces, and I'll bet that not all of your users have the same space needs, separate tablespaces for all your schemas could be quite a nightmare. Not to mention I don't think you'll get a single performance gain from it unless you had 3,500 drives and controllers to spread them out across.

A suggestion was made earlier to have a few data and index tablespaces. I agree with this 100%. I would arrive at the number based on the number of drives/controllers you have available; and I would hope you could at least get your user's data spread out across three different drive/controllers. I would keep all of a given user's data in the same data tablespace and it's corresponding index tablespace. Then after a while you should be able to see how balanced the load is and you could move users (schemas) to other drives if necessary.

BTW, I recently worked at a site with 1600+ schemas (users) with a total of about 1.5TB of space running on an HP V class server. No problems whatsoever.

Matt.

Sent via Deja.com
http://www.deja.com/ Received on Fri Jan 19 2001 - 07:36:52 CST