Re: truncate command 8.1.7. locally managed tablespaces

"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 Received on Thu May 22 2003 - 15:05:17 CDT