Re: LMT and DMT

"tingl" <one4all_at_all4one.not> wrote in message news:ogZ%9.8085
>
> I would object 1, partially accept 2, and totally accept 3.
>
> 1. To completely eliminate fragmentation, you can also make all extents
the
> same size in DMT,

And how, pray, do you expect to enforce this? If you have a default storage clause, I can over-ride it with a storage clause at the 'create table' level. If you use MINIMUM EXTENT, you are forced to supply multiples of the minimum extent if I supply a sufficiently large storage clause at the table level. You cannot *enforce* same-sized extents in DMT. Period. Ever.

> even though fragmentation is more of an issue in theory than in
> practice.

Nonsense. Fragmentation can be a massive waste of space. I once worked on a database where 3.4GB of disk space was unusable through fragmentation. On an 8GB tablespace. That's significant in anyone's book.

>
> 2. The effect of this [LMTs reduce (sometimes drastically) IO and locking
due to eliminating transactions against UET$ and FET$.] would depend on how well or poor the DMTs are configured.

No, it's got nothing to do with DMT configuration, intrinsically. However well you configure them, you cannot stop two tables wanting to acquire extents simultaneously. At which point, you have data dictionary contention, serialization of DD updates, and waits (and crap performance). The only way to possibly prevent that would be to create extents so massive no table ever wants to extend... and that's just plain daft and a massive waste of space.

>
> 3. This is where LMT shines but fewer extents is still better.

Why? This is the big one. WHY are fewer extents better? And you'd best be able to prove it. The number of extents a segments acquires is utterly irrelevant, within reasonable bounds (anything between about 1 and 1000 in LMT is OK).

>
> Wouldn't it be nice to use bitmap and at the same time having less
> restriction on extent sizes in
> the same tablespace.

What? This makes no sense. No, is the short answer. You want different extent sizes? Create different tablespaces... it's not hard.

HJR Received on Wed Feb 05 2003 - 06:08:42 CST