Re: locally managed tablespace & dictionary managed tablespace

Hi Howard
Thanks alot for the answer! It gives me another light on my oracle learning path.
I'm really appreciate it.

Regards,
Andi

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:BbQP9.12506$jM5.34484_at_newsfeeds.bigpond.com...
> 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 Mon Dec 30 2002 - 00:38:24 CST