Re: LMT and DMT

Hi Norm,

Thank you for your comments. Please see my response below.

> >> DMT gives you more options when it comes to extent sizes in a single
> >> tablespace.
>
> This is indeed true, DMTs allow anyone with create table privs to create
> totally random extent sizes that are of no use to any other object in
> the same tablespace. Once an extent is dropped and bits of it are used
> for other objects, then you get free space fragmentation. Having FSF
> leads to the problem where a tablespace has, say 100 MB free, but as it
> is all in randon sized chunks, the biggest of which is about, say 2 Mb,
> then trying to allocate an extent for one ofg your many different extent
> sizes will fail if the size required is bigger than the afore mentioned
> 2 Mb.
>
> Not only that, but any import into this tablespace can totally screw
> things up if the exporter has used COMPRESS=YES (the default) - this is
> a sure fire way to run out of space half way through an import - I know
> because I have a customer who insists that one extent is good and leads
> to much improved performance.
>
>

First I have to clarify. Although DMT allows you to create random size extents,
it does not prevent you from sizing extents appropriately. Fragmentation only
becomes a real problem when the extents are unreasonably sized and segments are being dropped or truncated frequently. This problem is further minimized by
merging smaller extents into larger ones.

> >> In the time we will continue to use DMT until we have to make a
> choice between uniform LMT - the one-size-fit-all
> >> approach or system LMT - the brain dead approach. :)
>
> Personally, LMT's have been the best thing to hit Oracle databases since
> the last best thing - they make life so much easier, and even our users
> notice a performance increase.
>

Again, how much better LMT is depends on how well or poorly DMT is sized. I have used DMT for many years and worked just great.

> Anyway, if you don't want to have 'one size fits all' then simply create
> a couple of other tablespace with their own 'one size' and remember, if
> you ask for initial of 2 Gb in a 64 Kb extent, you'll still get 2 Gb so
> where's the problem ?
>
> Even better, if you have 100 Mb free in an LMT then you can use it all
> even if it is spread all over the place - no such thing as FSF anymore -
> I love it.
>
>

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. Received on Wed Feb 05 2003 - 21:45:18 CST