Re: LMT and DMT

"tingl" <one4all_at_all4one.not> wrote in message news:iTk0a.2015$1q2.182538_at_newsread2.prod.itd.earthlink.net...
> I have heard of that approach before. It will probably result in more
> tablespaces.
> Let's say if I have 10 tablespaces for tables and 10 for indexes. If I
> divide each
> one into 3, now I have 30 for tables and 30 for indexes. To enforce this
> approach,
> I have to move objects back and forth between tablespaces as their sizes
> change.
> It just gets worse if I further divide the tablespaces.

However you'd be daft to divide each existing tablespace into 3, a much more likely recommendation would be that you create 3 new tablespaces based on extent size, spread across the same disks as you existing 10 tablespaces, and then move objects into these tablespaces from your existing 10 tablespaces. You end up with 3 tablespaces not 20, that looks like a big improvement to me.

Note here that I am assuming one application per database here. More apps would likely increase the number of tablespaces that you'd use. Even on the worst case though (you have ten apps with an index and a data tablespace each, and they all have a large range of object sizes so they each need 3 tablespaces with different extent sizes) there would be no need for more than 30 tablespaces ten apps * 3 extent sizes. Bear in mind this is a worst case scenario. lets be conservative for a moment and say you move objects when they have more than 100 extents.

a 64k tablespace will hold objects up to 6mb or so a 4mb tablespace will hold objects up to 400mb an 64mb tablespace will hold objects up to 6gb or so.

How likely is it that you would run ten apps with objects of 6gb or so in size on the same box?

cheers

--
Niall Litchfield
Oracle DBA
Audit Commission UK