Re: Multiple Tablespaces

Most datawarehouses I've worked on all have more than 100 tablespaces ... example - large tables containing historical data : 12 months - 12 partitions - that's 12 tablespaces for me you only need 10 of those to get to 120 tablespaces :-) And I do that for various reasons :
- loading & extent management : all my tablespaces are locally managed uniform sized ... each extent size is thought about and I use tablespaces to implement it
- performance : with multiple tablespaces I choose which tablespaces/segments get to sit on which disk, SSA loop, controller etc ... try figuring that one out
with 20 cowabunga tablespaces for 1TB - this is espescially nice when using parallel query and you have 100+ disks to play with (the extent striping amoung
Oracle datafiles just doesn't cut it)
- availability & recovery : if a tablespace goes offline it doesn't take half the warehouse down ...
if I need to recover :
- either by reload - no need to load 12 10GB partitions while you could have done it with 1 load if only you thought about it before - database recovery - if the situation ever occurs that you have to recover part of the database on an alternate location - try imagining a 1TB warehouse
with 10 tablespaces and one with 500 tablespace ... and you only need 1 partition recovered ... which one requires the least amount of space. - personal : I do not like the idea of 'logical partitions' - a partioned table in one tablespace ... what is that all about ... why partition in the first place ?

On the other hand : for non-warehouse databases I tend to go for the following approaches :
- either you as a DBA knows what tables are related to each other and you could group them according to that - depending on the application size you might end up with several tablespaces (developers seem to like this approach).... but 100 tablespaces in this case is rather large - group segments of the same size together in locally managed tablespaces (uniform sized) - for example tablespaces called 'small', 'medium', 'large', 'huge'
- for the passionate ones you could use a combination of both - for the performance freaks - if noticing frequent joins between tables within the same tablespace - create separate tablespaces and put the tablespaces
on seperate disks ... this way I might end up with a small tablespace but it's all for a good cause :-)
- the same goes for indexes ...

I agree - 25x10Mb tablespaces is over the top - but other than that - it all depends on what kind of database you're running. As a DBA you should take the responsibility of the decision for extra tablespaces or not - not some 'data architect' or 'capacity planning' guy... but
then again - I also believe the DBA should install and tune the complete server/storage and not only the database :-) I've seen VLDB databases with 64000 datafiles - and others with 5 tablespaces ... neither is good I believe ...

"Daniel A. Morgan" <damorgan_at_exesolutions.com> wrote in message news:3C56AED3.B3D7AE0E_at_exesolutions.com...
> I can't imagine any rational reason. But then, for that matter, I can't
> understand a production database in which new objects are added on a
> monthly basis. To me it sounds like a prima facia case of 'bad
> architecture'.
>
> I have worked on hundreds of applications at tens of companies ranging in
> size up to Fortune 500 and apps in the Terabyte range and never yet seen
> 100 tablespaces, much less 230+, except with something like Oracle
> Financials. but 25 tablespaces under 10MB, to me, is incomprehensible.
>
> Get a new architect.
>
> Daniel Morgan
>
>
>
> Dan Souza wrote:
>
> > We have a 600GB+ DSS. It grows (in terms of new objects added) on a
> > monthly basis. Our data architect is incredibly eager on multiple
> > tablespaces. So far, there are 230+, and there are more to come.
> > Practically every large object has it's own tablespace, and even small
> > ones - there are about 25 tablespaces under 10MB.
> >
> > Can anybody come up with a reasonable argument as to why so many
> > tablespaces are a good idea? I really can't see any advantages. We
> > backup using RMAN.
>
Received on Tue Jan 29 2002 - 17:14:46 CST