Re: Tables per Tablespace

"Ezr Vinh" <d_a_p_at_my-deja.com> wrote in message news:90ot33$1hj$1_at_nnrp1.deja.com...
> Howard,
>
> While I agree with your comments about segregating datafiles by
> tablespace so as to minimize i/o contention, I'm a bit puzzle by your
> last statement:
>
> > And whilst you could theoretically construct things such that
> > multiple applications are housed within a single database, each
> > separated from the others by using different tablespaces, it's
> > not what tablespaces were really designed for, and imho is a
> > performance nightmare in the making.
>
> Could you elaborate on this point? Are you suggesting using separate
> Oracle **instances** for multiple applications, with each app getting
> its own instance?

Exactly what I'm suggesting (and an inevitable outcome of proper relational database theory).

>Or do you mean to use seperate **schemas** for each
> app within a single instance? I know we're talking about this at a very
> high level, but in general I would think that having seperate instances
> is going to add considerably overhead.

Of course. But then, if you wish to run 5 different apps, I tend to think you should resource yourself accordingly.

>I would opt for a single,
> properly sized SGA.
>

Well, a single SGA means a single database, and as I said originally, whilst you *can* do that, but performance for any one app. is going to be compromised by the demands of the others. Theoretically (and somewhat simplistically), given that 5 (say) apps are going to be issuing their own SQL statements, your library cache should be 5 times bigger than it otherwise would be to accomodate them all, and prevent undue ageing out of execution plans -and if your SGA is going to be 5 times bigger than otherwise it would be, you might reasonably ask why you wouldn't simply create 5 dedicated SGAs in the first place.

> Theoretically speaking, I would seek to assign tables and indexes to
> tablespaces based on anticipated i/o activity without regard to which
> applications are actually hitting them. Example:
>
> TABLE_1: a big table with lots of reads/writes from App1.
> TABLE_2: a big table with lots of reads/writes from App2.
> TABLE_3: a static reference table used by App1, low I/O requirements
> TABLE_4: a static reference table used by App2, low I/O requirements
>
> I might be inclined to put the static reference tables (3 & 4) together
> in a tablespace, and put the large, growing data tables (1 & 2) each in
> their own tablespace.
>
> Comments?
>

If you are going down the 'multi-app/single database' route, yours is as good a layout as I've seen suggested.

Regards
HJR
> -Dave
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sat Dec 09 2000 - 15:03:47 CST