Tablespace Design

[maricel]

Which one is better in terms of producing optimum performance:

a) Single tablespace that contain multiple containers, say 5 containers of
100MB each container
or
b)Single tablespace that that contain single container, say 1 container of
500MB - same tablespace size as in (a).

Is there any trade-off?

Your help will be appreciated.

Thanks.
maricel

[Mark A]

"maricel" <ma*****@xtra.c o.nz> wrote in message
news:SL******** ************@ne ws.xtra.co.nz.. .

Which one is better in terms of producing optimum performance:

a) Single tablespace that contain multiple containers, say 5 containers of
100MB each container
or
b)Single tablespace that that contain single container, say 1 container of
500MB - same tablespace size as in (a).

Is there any trade-off?

Your help will be appreciated.

Thanks.
maricel

To make the most of using multiple containers, put them on different
physical hard drives (not just partitions on the same hard drive). So if you
have 2 hard drives, use two containers per tablespace. This assumes that
your drives are not already striped with RAID.

If your database is mostly OLTP or queries that access a small number of
rows via indexes, then it will not make much difference. But if you have a
IW database with a lot of tablespace scans or index scans (with prefetch
activity), optimum configuration will probably be noticeable.

Also, having large buffer pools reduces the effect that tablespace
configuration has on performance.

[Mark Yudkin]

With such small tablespaces, it's probably unimportant. If you have a lot of
drive arrays, partitioning the table over the drives for load balancing is
good for performance. But if you only hae a small number of drive arrays,
jsut separating tables and/or logs is normally adequate. With 500MB tables,
and 36GB / 72GB size drives being the smallest modern RAID drive sizes,
you're unlikely to have the hardware for these sorts of considerations.

"maricel" <ma*****@xtra.c o.nz> wrote in message
news:SL******** ************@ne ws.xtra.co.nz.. .

Which one is better in terms of producing optimum performance:

a) Single tablespace that contain multiple containers, say 5 containers of
100MB each container
or
b)Single tablespace that that contain single container, say 1 container of
500MB - same tablespace size as in (a).

Is there any trade-off?

Your help will be appreciated.

Thanks.
maricel