LMT is a locally managed tablespace, DMT is a dictionary managed tablespace.
LMT's are better for a lot of reasons, No Fragmentation, extent management, could go on.
In 9i LMT is the default, so unless you specified otherwise your system tablespace will be LMT which will automatically make everything else an LMT as well
> and in 9i I COULD see everything as LMT only ? Why
> Rgds
Hi There,
A search of the google archives will give you a whole bunch of hits on this subject.
The Concepts and Administrator's manuals discusses this in some depth.
Basically DMT store extent and free space info in the data dictionary causing possible performance issues and stress on the dd, and extents can be of any size causing possible fragmentation problems.
LMT store extent info in a number of bitmap blocks within the datafiles themselves resulting in a stress free dd, faster allocation/deallocation of extents and can be configured with a uniform extent size resulting in no fragmentation issues.
Depends on your version. Would I touch LMTs in 8.1.5? Nope. Would I touch DMTs in 8.1.7?? Nope.
These days (ie, by the time you get to 9i R2) there's not a lot of choice. LMTS are the default, and if you forget to specify otherwise at database creation time, you get an LMT SYSTEM, and you are forbidden ever afterwards from using DMTs in that database.
Which tells you that Oracle wants you to use LMTs because they are a good thing. They prevent "tablespace fragmentation" (which isn't a performance issue, but is definitely a waste of space issue). They prevent contention for the data dictionary when dozens of segments all decide to extend at the same time. They let you use ASSM, which is (probably) a very good thing in a RAC environment. They mean that we couldn't really give much of a damn about how many extents your segments acquire, because there are precius few performance penalties in going moderately beserk with the number of extents anymore.
And all of that means that the DBA can more or less give up worrying about space management issues in the database.
And although I exaggerate slightly, it's not by much.
> > and in 9i I COULD see everything as LMT only ? Why
> > Rgds
> Hi There,
> A search of the google archives will give you a whole bunch of hits on this > subject.
> The Concepts and Administrator's manuals discusses this in some depth.
> Basically DMT store extent and free space info in the data dictionary > causing possible performance issues and stress on the dd, and extents can be > of any size causing possible fragmentation problems.
> LMT store extent info in a number of bitmap blocks within the datafiles > themselves resulting in a stress free dd, faster allocation/deallocation of > extents and can be configured with a uniform extent size resulting in no > fragmentation issues.
> thing. They prevent "tablespace fragmentation" (which isn't a performance > issue, but is definitely a waste of space issue).
Are you sure? This is true in case of uniform LMTs, not in system managed LMTs. You can mimic uniform LMTs with DMTs, too, but choosing 3 TBS (e.g. a 64k TBS for small objects, a 1M and a 8M for larger objects).
> They prevent contention > for the data dictionary when dozens of segments all decide to extend at the > same time.
Ok, if you do a lot of space management or you have 10.000s of extents.
I don't like LMTs for the following reasons: - with uniform I need to know the size of the object from the beginning - with system managed LMTs I end up with fragmentation (64k, 1M, 8M extents) and a huge number of extents - access to dba_extents is unacceptable slow when >2000 extents are allocated
> <snip> > > thing. They prevent "tablespace fragmentation" (which isn't a performance > > issue, but is definitely a waste of space issue). > Are you sure? This is true in case of uniform LMTs, not in system managed > LMTs. You can mimic uniform LMTs with DMTs, too, but choosing 3 TBS (e.g. a > 64k TBS for small objects, a 1M and a 8M for larger objects).
This is sort of true, but misses the enforcement aspect of LMT's. A DMT can specify default storage clauses but anyone with the create object privilege can go ahead and specify their own storage clause which takes priority and causes fragmentation.
> > They prevent contention > > for the data dictionary when dozens of segments all decide to extend at > the > > same time. > Ok, if you do a lot of space management or you have 10.000s of extents.
> I don't like LMTs for the following reasons: > - with uniform I need to know the size of the object from the beginning
I'd argue that you need to know that with DMTs as well. (at least to within an order of magnitude which is all the precision you need for LMTs)
> - with system managed LMTs I end up with fragmentation (64k, 1M, 8M extents) > and a huge number of extents
fair comment, uniform is the way to go.
> - access to dba_extents is unacceptable slow when >2000 extents are > allocated
This seems somewhat odd, do you mean lots of objects with >2000 extents,
>2000 extents in total in dba_extents.
Cheers.
-- Niall Litchfield Oracle DBA Audit Commission UK
> <snip> > > thing. They prevent "tablespace fragmentation" (which isn't a performance > > issue, but is definitely a waste of space issue). > Are you sure? This is true in case of uniform LMTs, not in system managed > LMTs. You can mimic uniform LMTs with DMTs, too, but choosing 3 TBS (e.g. a > 64k TBS for small objects, a 1M and a 8M for larger objects).
Uniforms never fragment, and autoallocates might, that's true. But the autoallocate algorithm is pretty sensible, and you end up with 64K, 1M, 8M 64M extents... and all of those are divsors of the other, so if I truncate a table and free up 3 1M extents, than I am going to be able to fit plenty of 64K extents within it without a problem.
Compare that with DMTs where (in the absence of MINIMUM EXTENT) you could have 84K extents, 93K extents, 17456K extents and God knows what else.
> > They prevent contention > > for the data dictionary when dozens of segments all decide to extend at > the > > same time. > Ok, if you do a lot of space management or you have 10.000s of extents.
TEMP anyone? (OK, only the initial allocations, perhaps) Rollback segments anyone? (Potentially all the time).
> I don't like LMTs for the following reasons: > - with uniform I need to know the size of the object from the beginning
Why? If you're not sure, bung it in a 64K eextent tablespace. When you acquire 100 extents, move it to the 1M tablespace and so on. There is less requirement to know your sizes in LMT than there ever was in DMT.
> - with system managed LMTs I end up with fragmentation (64k, 1M, 8M
extents)
Mere;y having differently-sized extents is *not* fragmentation. It's whether a segment can make use of the space freed up by the truncating or dropping of a segment. The use of those multiples means fragmentation is minimised in autoallocates.
> and a huge number of extents
Who cares? The number of extents isn't an issue in LMTs. It *was* an issue in DMTs, where a huge number of extents meant a huge number of records in UET$ and FET$ and corresponding chaining in the data dictionary. But we're not using those tables now.
> - access to dba_extents is unacceptable slow when >2000 extents are > allocated
Depends on the system, I think. But if you're worried about it, move the table to a bigger-extent LMT and thus reduce the number of extents.
> Even on 9i, I used DMTs (not for undo and temp).
I'd get out of the habit if I were you. It's quite clear that DMTs will be abolished at some point in the future, as srvmgrl and internal were before them.
> > > thing. They prevent "tablespace fragmentation" (which isn't a > performance > > > issue, but is definitely a waste of space issue). > > Are you sure? This is true in case of uniform LMTs, not in system managed > > LMTs. You can mimic uniform LMTs with DMTs, too, but choosing 3 TBS (e.g. > a > > 64k TBS for small objects, a 1M and a 8M for larger objects).
> This is sort of true, but misses the enforcement aspect of LMT's. A DMT can > specify default storage clauses but anyone with the create object privilege > can go ahead and specify their own storage clause which takes priority and > causes fragmentation. > Cheers.
Well, I think Daniel offered an excellent solution in another thread (though I doubt he realized it at the time). He asked if we should go back to 3x5 cards. I think that's an excellent idea! At least 3x5 cards have a uniform extent size. Now if I can just remember how to make one of those smiley faces that shows I'm being facetious.
PS. My apologies to Daniel for quoting him out of context and completely obfuscating his other post. -- /Karsten
> and I would say that the above is too simplistic a comment.
> There are advantages to LMT that make it easier to have a "properly > configured LMT" and harder for the same to be said about DMT.
> I still think LMT is better ...
> Cheers
> Richard
Hi Richard,
I am not sure what you meant by too simplistic. My intention was to make it less simplistic than just "LMT is better.". Anyway there is a tradeoff between ease of maintenance and flexibility. LMT and DMT each has pros and cons. Neither is better than the other in all situations.
> > and I would say that the above is too simplistic a comment.
> > There are advantages to LMT that make it easier to have a "properly > > configured LMT" and harder for the same to be said about DMT.
> > I still think LMT is better ...
> > Cheers
> > Richard
> Hi Richard,
> I am not sure what you meant by too simplistic. My intention was to make it > less simplistic > than just "LMT is better.". Anyway there is a tradeoff between ease of > maintenance and > flexibility. LMT and DMT each has pros and cons. Neither is better than the > other in all > situations.
> Tingl
I'm inclined to disagree thus I will. I can't think of a single thing a DMT does better than an LMT unless the goal is tablespace fragmentation. And from what I've seen ... it appears that Oracle agrees.
> I'd get out of the habit if I were you. It's quite clear that DMTs will be > abolished at some point in the future, as srvmgrl and internal were before > them.
> Regards > HJR
> > Stephan
And from what I hear ... in the not too distant future.
> I am not sure what you meant by too simplistic. My intention was to make it > less simplistic > than just "LMT is better.". Anyway there is a tradeoff between ease of > maintenance and > flexibility. LMT and DMT each has pros and cons. Neither is better than the > other in all > situations.
If you accept that
1) LMTs enforce standard storage conditions and so prevent fragmentation 2) LMTs reduce (sometimes drastically) IO and locking due to eliminating transactions against UET$ and FET$. 3) LMTs do not suffer even with large numbers of extents.
I find it difficult to envisage any circumstance when a DMT would be preferred. I have seen people object to all of the above (though rarely 2).
-- Niall Litchfield Oracle DBA Audit Commission UK
> > and I would say that the above is too simplistic a comment.
> > There are advantages to LMT that make it easier to have a "properly > > configured LMT" and harder for the same to be said about DMT.
> > I still think LMT is better ...
> > Cheers
> > Richard
> Hi Richard,
> I am not sure what you meant by too simplistic. My intention was to make it > less simplistic > than just "LMT is better.". Anyway there is a tradeoff between ease of > maintenance and > flexibility. LMT and DMT each has pros and cons. Neither is better than the > other in all > situations.
Hi Tingl,
What can a DMT do that can't be performed or managed easier with a LMT ?
This of course is all going to be a rather moot point as with 9.2, with a System LMT, DMT are not even possible and with 10i they'll probably be gone for good.
> > I am not sure what you meant by too simplistic. My intention was to make > it > > less simplistic > > than just "LMT is better.". Anyway there is a tradeoff between ease of > > maintenance and > > flexibility. LMT and DMT each has pros and cons. Neither is better than > the > > other in all > > situations.
> If you accept that
> 1) LMTs enforce standard storage conditions and so prevent fragmentation > 2) LMTs reduce (sometimes drastically) IO and locking due to eliminating > transactions against UET$ and FET$. > 3) LMTs do not suffer even with large numbers of extents.
> I find it difficult to envisage any circumstance when a DMT would be > preferred. I have seen people object to all of the above (though rarely 2).
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, even though fragmentation is more of an issue in theory than in practice.
2. The effect of this would depend on how well or poor the DMTs are configured.
3. This is where LMT shines but fewer extents is still better.
Wouldn't it be nice to use bitmap and at the same time having less restriction on extent sizes in the same tablespace.
> > > and I would say that the above is too simplistic a comment.
> > > There are advantages to LMT that make it easier to have a "properly > > > configured LMT" and harder for the same to be said about DMT.
> > > I still think LMT is better ...
> > > Cheers
> > > Richard
> > Hi Richard,
> > I am not sure what you meant by too simplistic. My intention was to make > it > > less simplistic > > than just "LMT is better.". Anyway there is a tradeoff between ease of > > maintenance and > > flexibility. LMT and DMT each has pros and cons. Neither is better than > the > > other in all > > situations.
> Hi Tingl,
> What can a DMT do that can't be performed or managed easier with a LMT ?
> This of course is all going to be a rather moot point as with 9.2, with a > System LMT, DMT are not even possible and with 10i they'll probably be gone > for good.
> Cheers
> Richard
Hey Richard,
DMT gives you more options when it comes to extent sizes in a single tablespace.
What Oracle wants to do in the future is really besides the point. I am not even sure if we will use Oracle if it comes to 10i. In the mean time we will continue to use DMT until we have to make a choice between uniform LMT - the one-size-fit-all approach or system LMT - the brain dead approach. :)
"tingl" <one4...@all4one.not> wrote in message news:ogZ%9.8085>
<snip>
> 1. To completely eliminate fragmentation, you can also make all extents the > same size in DMT, > even though fragmentation is more of an issue in theory than in > practice.
You can do this, but you can't enforce it. There is no way to stop me doing create table ah_ha_stuffed_you(col1 varchar2(200)) storage(initial 64k next 5m pctincrease 75 minextents 10); should I suffer a momentary aberation. In a LMT the table will still get appropriately sized extents.
Personally, where possible, I like practice to mirror theory.
-- Niall Litchfield Oracle DBA Audit Commission UK
Posted At: Wednesday, February 05, 2003 1:33 AM Posted To: server Conversation: LMT and DMT Subject: Re: LMT and DMT
>> DMT gives you more options when it comes to extent sizes in a single >> tablespace.
This is indeed true, DMTs allow anyone with create table privs to create totally random extent sizes that are of no use to any other object in the same tablespace. Once an extent is dropped and bits of it are used for other objects, then you get free space fragmentation. Having FSF leads to the problem where a tablespace has, say 100 MB free, but as it is all in randon sized chunks, the biggest of which is about, say 2 Mb, then trying to allocate an extent for one ofg your many different extent sizes will fail if the size required is bigger than the afore mentioned 2 Mb.
Not only that, but any import into this tablespace can totally screw things up if the exporter has used COMPRESS=YES (the default) - this is a sure fire way to run out of space half way through an import - I know because I have a customer who insists that one extent is good and leads to much improved performance.
<SNIP> >> In the time we will continue to use DMT until we have to make a choice between uniform LMT - the one-size-fit-all >> approach or system LMT - the brain dead approach. :)
Personally, LMT's have been the best thing to hit Oracle databases since the last best thing - they make life so much easier, and even our users notice a performance increase.
Anyway, if you don't want to have 'one size fits all' then simply create a couple of other tablespace with their own 'one size' and remember, if you ask for initial of 2 Gb in a 64 Kb extent, you'll still get 2 Gb so where's the problem ?
Even better, if you have 100 Mb free in an LMT then you can use it all even if it is spread all over the place - no such thing as FSF anymore - I love it.
> Anyway, if you don't want to have 'one size fits all' then simply create > a couple of other tablespace with their own 'one size' and remember, if > you ask for initial of 2 Gb in a 64 Kb extent, you'll still get 2 Gb so > where's the problem ?
Evening Norm,
Add to all you've said the fact that such extents result in substantially less overheads due to the in-file bitmap rather than dml to the dd, less stress and contention in the dd, less access overheads within the dd, less issues with cluster overflows in the dd, less issues with dynamic extent allocations/deallocations, etc. etc. etc. and one begins to understand why maybe Oracle is getting rid of DMTs.
Then again, this might be one of those things we may just have to agree to disagree with our friend Tingl.
"tingl" <one4...@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.
I'm happy to agree to disagree - I've used LMTs since I migrated up to 817 and have absolutely no complaints and no hope of ever returning to DMTs. If Tingl (great name by the way !) wants to stay on DMTs then each to their own.
Me, I love em !
Cheers, Norman.
PS. Fires all gone now - we're not getting any news at the moment from Australia since the Shuttle broke up.
> DMT gives you more options when it comes to extent sizes in a single > tablespace.
Come off it! WHY would you want 'more options' along these lines? give me one good reason why you think it desirable to be able to create 500 segments with 500 different extent sizes in the same tablespace.
There are *no* such good reasons. It all comes down to design, and if your particular business "needs" so many different extent sizes, then it's because someone doesn't know what they're talking about.
> What Oracle wants to do in the future is really besides the point.
No it's not, actually. The fact that they will abolish DMTs means that they recognise the inefficiencies associated with them, and wish to eliminate the inefficiencies. You'd be a fool not to want to eliminate them also.
>I am not > even sure > if we will use Oracle if it comes to 10i.
Fair enough. But that doesn't negate the fact that Oracle (in this particular case, just for once) isn't pushing LMTs because marketing thinks it sounds good, but because there are storage and performance benefits in making the switch. Not to mention less hassles for DBAs. Though I realise it makes it a bit tough for any DBA who is desperately searching for a justification for their job, and thinks 'space management' is a compelling argument.
>In the mean time we will continue > to use DMT > until we have to make a choice between uniform LMT - the one-size-fit-all > approach
What on earth is 'one size fits all' about LMTs? Are you in any way prevented from creating a tablespace where the uniform size is 97K? Or 4302K? or 2636K? No you are not.
> or system LMT - the brain dead approach. :)
What's brain dead is claiming that auto-allocate is brain-dead. It is a very efficient, and rather elegant algorithm that has all the benefits of LMTs regarding no contention on the Data Dictionary, and yet manages to utterly minimise the possibility of fragmentation.
No one (apart from Oracle Corporation) will be forcing you to use LMTs in the near future, so I am not suggesting that you're not entitled to your opinion, or that others shouldn't feel free to share it. But please don't post bunkum about LMTs capabilites, or the 'advantages' of DMTs. Nothing you have posted stands up to technical scrutiny, and some of things you've posted just invite the response: you apparently don't know how to manage a database. Which I'm sure is not true: but there's a difference between a genuine business need and base prejudice.
> I'm happy to agree to disagree - I've used LMTs since I migrated up to > 817 and have absolutely no complaints and no hope of ever returning to > DMTs. If Tingl (great name by the way !) wants to stay on DMTs then each > to their own.
> Me, I love em !
Hey Norman, this is a concern.
Me, I love David Bowie, watching "The Bill", dreaming of Crystal Palace winning the FA Cup, my family, Indian food and my teddy, Arnold (in no particular order).
But LMT and myself have purely a business relationship ;)
> Cheers, > Norman.
> PS. Fires all gone now - we're not getting any news at the moment from > Australia since the Shuttle broke up.
All the fires close to Canberra are all but gone but there's still a few nasty ones to the south in the mountains.
I can live with Indian food - seeing as I actually live in Bradford, West Yorkshire which is home to some of the finest Indian food on the planet. (And also to some food claiming to be Indian but not known outside the UK - Chicken Tikka Masala for one !)
I don't have a teddy - but my wife has a few (of both kinds !!!)
-----Original Message----- From: Richard Foote [mailto:richard.fo...@bigpond.com]
Posted At: Wednesday, February 05, 2003 1:56 PM Posted To: server Conversation: LMT and DMT Subject: Re: LMT and DMT
Hey Norman, this is a concern.
Me, I love David Bowie, watching "The Bill", dreaming of Crystal Palace winning the FA Cup, my family, Indian food and my teddy, Arnold (in no particular order).
But LMT and myself have purely a business relationship ;)
All the fires close to Canberra are all but gone but there's still a few nasty ones to the south in the mountains.
|
