Re: locally managed tablespace & dictionary managed tablespace

And thank you Jim.

Now onto serious matters (like actually answering the original question).

There are no occasions when dictionary managed tablespace (DMT) is a better choice than locally managed tablespace (LMT).

The real benefits of LMT are: (a) reduced contention on the data dictionary when many segments need to allocate or de-allocate space at the same time (in DMT, all such allocations and de-allocations are handled by expensive DML updates to the UET$ and FET$ tables in the data dictionary). (b) lack of concern about the number of extents a segment acquires. In DMT, extent acquisition means (see above) inserts on UET$. If a segment acquires a bazillion extents, that means tens of thousands of inserts of new records on UET$. When any segment acquires a gazillion records, trouble is brewing from a maintenance perspective. Having that happen on your data dictionary is not pretty. Related to this is a performance issue: lots of extents in DMT means that your data dictionary is at risk of 'chaining', because UET$ is actually a logical table stored within an index cluster: and when you create index clusters, you have to roughly predict the total size of all related records to be housed within the cluster. And Oracle's algorithm for that prediction is that no segment should acquire more than 5 extents. More than that, and you risk chaining on the dictionary cluster. (c) Related issue: space management is infinitely easier in LMT than DMT. You don't have to worry about the number of extents acquired by a segment, so you don't have to sit there for a week worrying about whether you should go for 64K extents or 64M ones. If you pick 64K ones, and the table goes beserk acquiring thousands of extents as a result, who cares? LMTs certainly don't. (d) LMTs with the uniform allocation policy never, ever fragment. And your developers can do their worst suggesting that table X should have INITIAL 73K NEXT 1536K, whilst table Y should have INITIAL 74623K NEXT 12K: the LMT will just ignore their ridiculousness, and allocate identically-sized extents based on the UNIFORM SIZE policy for that tablespace. No fragmentation means no (or little) re-organization, which is an outrageously expensive, I/O intensive/ downtime-inducing exercise in futility. Not having to do such shenanigans is a definite bonus.

The ONLY possible good thing about DMTs is that a badly-designed LMT can chew through disk space like it's going out of fashion. I once saw a colleague install an application which he expected to use up a mere 85MB. Because I'd just shown him the wonders of LMTs (and my example had used UNIFORM SIZE 1M) his install actually took up over 758MB. The reason? That particular application used dozens and dozens of small lookup tables, each containing no more than a few tens of records. Accordingly, the install script said things like 'INITIAL 16K'... which is exactly what he would have gotten in DMT. But of course ijn this case, the pathetically small tables each got allocated an entire 1MB.

However, once I'd shown said colleague that UNIFORM SIZE could take arguments like 64K, 256K, 1M and 8M (and once he'd edited the install script to direct each table to the right tablespace) we brought that installation down to a mere 94MB. Still a bit bigger than what he'd got in DMT, but only by 10MB.

Otherwise, I can't think of a single reason why you *wouldn't* want to use LMT. Oh.... and by the way, in 9i Release 2, if your SYSTEM tablespace is locally managed, you can't create ANY dictionary-managed tablespaces anyway. That should give you a clue what's going to happen in future releases (maybe 10i, maybe not... I wouldn't know). But eventually, DMTs will simply be abolished.

So you might as well get into the swing of things right now!

Regards
HJR "Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message news:9mPP9.160621$qF3.11566_at_sccrnsc04...
> Don't use locally managed tablespaces.
> Jim
> "andi" <andi_at_a.com> wrote in message
> news:auodnb$7d5$1_at_mawar.singnet.com.sg...
> > Hi,
> > newbie question.
> > I've read some books which mentioned about advantages of locally-managed
> > tablespace over dictionary-managed tablespace.
> > My question is, are there advantages of dictionary-managed tablespace
over
> > locally-managed tablespace ?
> > or when should we use dictionary-managed tablespace instead of
> > locally-managed tablespace ?
> >
> > TIA,
> > Andi
> >
> >
> >
>
>
Received on Sun Dec 29 2002 - 22:55:23 CST