Re: Tables per Tablespace

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? 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. I would opt for a single, properly sized SGA.

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?

-Dave

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Dec 07 2000 - 14:48:37 CST