Re: truncate command 8.1.7. locally managed tablespaces

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:OU9za.40136$1s1.566383_at_newsfeeds.bigpond.com...
>
> "Richard Q" <rquintin_at_vt.edu> wrote in message
> news:81cbe08.0305221104.3ff06461_at_posting.google.com...
> > "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> > > The key advantage of LMTs is that they avoid a potential single point
of
> > > contention on the data dictionary (but you'd have to have dozens of
> segments
> > > all simultaneously extending before that was an issue in the first
> place).
> >
> > This is just the statement I've been looking for. Everyone keeps
> > talking about how LMTs are the best thing since sliced bread, but no
> > one is giving any quantifiable benefits.
> >
> > Is it worth moving a 100G+ db to LMT? Sure we'll do it eventually,
> > but the impact it has on performance is going to dictate how soon.
> >
> > What metrics should I be looking at to determine the performance
> > benefits of LMT?
>
> Do you (1) ever have a segment run out of space during normal running,
when
> you know there's space to spare in the tablespace? Fragmentation doesn't
> happen in LMTs, and therefore they've got to be a good thing.
>
> Do you (2) have segments which are subject to continual inserts, and which
> either (a) you umm-and-ahh about getting extent sizes 'correct' so that
they
> don't acquire extents too often or (b) because you didn't carefully choose
a
> 'correct' extent size, the insert performance is sometimes a bit peaky?
>
> Extent acquisition is an expensive operation in DMT. It's full-blown scans
> of the FET$ table, and inserts and deletes on FET$ and UET$... and inserts
> and deletes are expensive animals in terms of the amount of redo they
> generate; and deletes are expensive in the amount of undo they generate.
> Flipping bits in LMTs is much cheaper, so the costs of dynamic extension
of
> segments are negligible (which is why worrying about extent sizes, and did
> you get the 'right' one, is a thing of the past).
>
> Do you (3) spend ages monitoring segment sizes, growth, free space and so
> on? If so, it's effort that can be put to better uses by moving to LMT,
> where you tend not to care about such things anywhere near so much. This
> isn't a performance thing, but a (potentially) huge management
> convenience... but if you don't do much of the inconvenient stuff in the
> first place, then you don't stand to reap much benefit, either!
>
> If you don't have constant dynamic extent acquisition; if you don't have
> fragmentation; if you don't have constantly growing tables; if you don't
> perform frequent bulk-loads... then I doubt you'll see any performance
> benefit at all from switching to LMTs, and I'd suspect that converting a
> 100GB+ of stable, fairly static, database over to using them is, frankly,
a
> bit of a waste of time.
>
> I might still do such a conversion at my leisure, only because Oracle has
> clearly flagged that DMTs will cease to exist in the future, and I might
as
> well get ready for it whilst I can.
>
> And if I was suffering from acute free list contention, I might well want
to
> use ASSM to fix it... which would necessitate at least the use of some
LMT,
> because ASSM only works in LMT. (And who knows what other new features
might
> come along that only work in LMTs?)
>
> And if I had a large temporary tablespace, I would probably switch that to
> the locally-managed tempfile version, because being able to re-create it
in
> seconds instead of restoring-and-recovering it, or re-creating it in
> minutes, means recovery time comes down.
>
> None of which answers your specific question about metrics for measuring
the
> performance improvement to be gained with LMTs over DMTs, largely because
> they are few and far between, and you'd have a hard time measuring them
> anyway, unless you are suffering from particular chronic problems in the
> first place.
>
> Would I want to create DMTs in a new database? Nah. LMTs are convenient,
and
> the way of the future, so I wouldn't be that daft.
>
> If I had a 10GB database, would I convert? Yes, because that's small, and
> conversion (ie, move tables between two tablespaces) isn't going to kill
me
> or the app.
>
> With a 100GB database, I'd be much more selective about it, converting
only
> that which suffers from fragmentation, or constant extent acquisition, or
> frequent bulk-loads, or free list contention; and the rest I'd probably
only
> do at all if the organisation had plans to move to 10i in the medium term.
>
> Regards
> HJR
>
>

Howard,

In this thread we seem to be pretty much agreed up to now, but I'm not quite so sure about what Jonathan refers to as 'Ass Management'.

Regards,
Paul Received on Thu May 22 2003 - 17:36:15 CDT