LMT and DMT
vbaskar
What is the difference between LMT and DMT ?
Which one is better ?
and in 9i I COULD see everything as LMT only ? Why
Rgds
--
Posted via http://dbforums.com
David Sharples
"vbaskar" <membe...@dbforums.com> wrote in message
news:2468993.1...@dbforums.com...
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
Richard Foote
>
> Which one is better ?
>
> 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.
LMT is better.
Cheers
Richard
Howard J. Rogers
>
> Which one is better ?
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.
LMTs every time, therefore.
Regards
HJR
tingl
vice versa.
"Richard Foote" <richar...@bigpond.com> wrote in message
news:8iP_9.38221$jM5....@newsfeeds.bigpond.com...
Richard Foote
news:MZU_9.2302$ek4.2...@newsread2.prod.itd.earthlink.net...
> I would say a properly sized LMT is better than a poorly managed DMT, and
> vice versa.
>
Hi Tingl,
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
Stephan Bressler
<snip>
> thing. They prevent "tablespace fragmentation" (which isn't a performance
> issue, but is definitely a waste of space issue).
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
Even on 9i, I used DMTs (not for undo and temp).
Stephan
Niall Litchfield
news:b1lrf9$l4r$1...@news.mch.sbs.de...
>
> <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
Howard J. Rogers
"Stephan Bressler" <stephan....@siemens.com> wrote in message
news:b1lrf9$l4r$1...@news.mch.sbs.de...
>
> > 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.
Regards
HJR
>
> Stephan
>
>
>
>
Karsten Farrell
> "Stephan Bressler" <stephan....@siemens.com> wrote in message
> news:b1lrf9$l4r$1...@news.mch.sbs.de...
> >
> 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.
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
>
>
>
(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
tingl
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
DA Morgan
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.
Daniel Morgan
DA Morgan
>
> 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.
Daniel Morgan
Niall Litchfield
> 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.
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).
Richard Foote
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
tingl
"Niall Litchfield" <n-litc...@audit-commission.gov.uk> wrote in message
news:3e3f9640$0$232$ed9e...@reading.news.pipex.net...
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.
tingl
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
Niall Litchfield
<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.
Norman Dunbar
Hmmm - comments inline !
Cheers,
Norman.
-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman...@LFS.co.uk
Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------
-----Original Message-----
From: tingl [mailto:one...@all4one.not]
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.
Cheers,
Norman.
Richard Foote
news:E2F6A70FE45242488C86...@lnewton.leeds.lfs.co.uk...
<snip snap away>
> 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.
Cheers
Richard
Howard J. Rogers
"tingl" <one...@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.
HJR
Norman Dunbar
Aren't time zones wonderful :o)
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.
-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman...@LFS.co.uk
Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------
-----Original Message-----
From: Richard Foote [mailto:richar...@bigpond.com]
Posted At: Wednesday, February 05, 2003 12:44 PM
Posted To: server
Conversation: LMT and DMT
Subject: Re: LMT and DMT
Evening Norm,
<SNIP of highly accurate info>
Howard J. Rogers
>
> 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.
HJR
Richard Foote
news:E2F6A70FE45242488C86...@lnewton.leeds.lfs.co.uk...
>
> Aren't time zones wonderful :o)
>
>
> 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.
And the air is fresh and clear again :)
Richard
Norman Dunbar
I think I'll stick to LMTs then !
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 !!!)
Glad to hear the fires are all but out.
Cheers,
Norman.
-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman...@LFS.co.uk
Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------
-----Original Message-----
From: Richard Foote [mailto:richar...@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
Frank
> Hi Richard,
>
> I think I'll stick to LMTs then !
>
> 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 !)
Good afternoon, (would be a nice habit to start with...)
Had a decent one in The Netherlands, not long ago...
That's not UK, mind you, we're at the other side of
the North Sea - bloddy forreners to the most of you. ;-?
Cheers, Frank
>
> I don't have a teddy - but my wife has a few (of both kinds !!!)
>
> Glad to hear the fires are all but out.
>
Me too.
Frank
Good afternoon Howard,
I think this remark is along the route of 'using autoextending
tablespaces is lazy DBA approach'.
I -personally- disagree, provided an automated
system monitoring tools is in place and being used.
Just returned from a client with performance issues, and thay
have all users granted dba role, or "else the software won't run".
Resulted in a huge amount of indexes being created in SYSTEM tablespace,
which is now totally cluttered. Some objects had ##,### extents...
Advised to go LMT... And upgrade to the latest patch level.
Among others.
Cheers,
Frank
Norman Dunbar
I know where the Netherlands is - it's that flat bit near the sea isn't
it.
Or is that 'under' the sea :o)
Cheers,
Norman.
-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman...@LFS.co.uk
Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------
-----Original Message-----
From: Frank [mailto:fvanb...@netscape.net]
Posted At: Wednesday, February 05, 2003 4:43 PM
Posted To: server
Conversation: LMT and DMT
Subject: Re: LMT and DMT
Frank
Evening, Norman,
Yes, partially. Behind dykes and dunes. Our main airport
is 'under the sea' (Some 2 meters/6 feet below sealevel, iirc)
You know the proverb:
"God created the world - apart from Holland. The Dutch did
that themselves"
DA Morgan
Frank wrote:
> <snipped>
>
>
> I think this remark is along the route of 'using autoextending
> tablespaces is lazy DBA approach'.
> I -personally- disagree, provided an automated
> system monitoring tools is in place and being used.
>
Doesn't seem like you disagree at all.
> Just returned from a client with performance issues, and thay
> have all users granted dba role, or "else the software won't run".
> Resulted in a huge amount of indexes being created in SYSTEM tablespace,
> which is now totally cluttered. Some objects had ##,### extents...
> Advised to go LMT... And upgrade to the latest patch level.
> Among others.
>
> Cheers,
>
> Frank
Giving someone the DBA role has nothing to do with anything being created in the
SYSTEM tablespace. What were they thinking? Or were they?
Daniel Morgan
Frank
I feel like I am missing out on two things here...
1) I do not think autoextending tablespaces should be discarded
as 'a lazy DBA's approach'. I disagree with that; I find then
useful in remotely controlled environments with a control room
monitoring system resources.
2) "What were they thinking? Or were they?" Like: were they
[thinking AT ALL]? Am I missing the(/a) point here?
Goin' for a beer, smoke a pipe, nighty-night!
Frank
Jonathan Lewis
Here's a dinky little joke -
Checked on 9.2.0.1 so far.
If your system tablespace is an LMT,
every tablespace has to be an LMT, except
for transported DMTs which have to be
permanently read only.
So why does smon STILL run a query
every five minutes to see if there are
any dictionary managed tablespace
with free extents that could be coalesced ?
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )
____UK_______March 19th
____USA_(FL)_May 2nd
Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )
____USA_(CA, TX)_August
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Richard Foote wrote in message
<8iP_9.38221$jM5....@newsfeeds.bigpond.com>...
>
>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.
>
>LMT is better.
>
>Cheers
>
>Richard
>
>
tingl
Just recreate the table. Small price to pay for a momentary aberation. :)
tingl
Thank you for your comments. Please see my response below.
> >> 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.
>
>
First I have to clarify. Although DMT allows you to create random size
extents,
it does not prevent you from sizing extents appropriately. Fragmentation
only
becomes a real problem when the extents are unreasonably sized and segments
are being dropped or truncated frequently. This problem is further minimized
by
merging smaller extents into larger ones.
> >> 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.
>
Again, how much better LMT is depends on how well or poorly DMT is sized.
I have used DMT for many years and worked just great.
> 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.
>
>
I have heard of that approach before. It will probably result in more
tablespaces.
Let's say if I have 10 tablespaces for tables and 10 for indexes. If I
divide each
one into 3, now I have 30 for tables and 30 for indexes. To enforce this
approach,
I have to move objects back and forth between tablespaces as their sizes
change.
It just gets worse if I further divide the tablespaces.
Norman Dunbar
the fact that Schipol is below sea level would explain why it seems to
take longer to land there than anywhere else ! Last time I was landing
there, it seemed to take forever to get down.
Cheers,
Norm.
PS. Love the proverb !
-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman...@LFS.co.uk
Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------
-----Original Message-----
From: Frank [mailto:fvanb...@netscape.net]
Posted At: Wednesday, February 05, 2003 6:09 PM
Posted To: server
Conversation: LMT and DMT
Subject: Re: LMT and DMT
Niall Litchfield
> I have heard of that approach before. It will probably result in more
> tablespaces.
> Let's say if I have 10 tablespaces for tables and 10 for indexes. If I
> divide each
> one into 3, now I have 30 for tables and 30 for indexes. To enforce this
> approach,
> I have to move objects back and forth between tablespaces as their sizes
> change.
> It just gets worse if I further divide the tablespaces.
However you'd be daft to divide each existing tablespace into 3, a much more
likely recommendation would be that you create 3 new tablespaces based on
extent size, spread across the same disks as you existing 10 tablespaces,
and then move objects into these tablespaces from your existing 10
tablespaces. You end up with 3 tablespaces not 20, that looks like a big
improvement to me.
Note here that I am assuming one application per database here. More apps
would likely increase the number of tablespaces that you'd use. Even on the
worst case though (you have ten apps with an index and a data tablespace
each, and they all have a large range of object sizes so they each need 3
tablespaces with different extent sizes) there would be no need for more
than 30 tablespaces ten apps * 3 extent sizes. Bear in mind this is a worst
case scenario. lets be conservative for a moment and say you move objects
when they have more than 100 extents.
a 64k tablespace will hold objects up to 6mb or so
a 4mb tablespace will hold objects up to 400mb
an 64mb tablespace will hold objects up to 6gb or so.
How likely is it that you would run ten apps with objects of 6gb or so in
size on the same box?
cheers
Norman Dunbar
comments inline as ever !
Cheers,
Norm.
-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman...@LFS.co.uk
Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------
-----Original Message-----
From: tingl [mailto:one...@all4one.not]
Posted At: Thursday, February 06, 2003 3:45 AM
Posted To: server
Conversation: LMT and DMT
Subject: Re: LMT and DMT
>> First I have to clarify. Although DMT allows you to create random
size
>> extents, it does not prevent you from sizing extents appropriately.
Fragmentation
>> only becomes a real problem when the extents are unreasonably sized
and segments
>> are being dropped or truncated frequently. This problem is further
minimized
>> by merging smaller extents into larger ones.
Well, as long as you, the dba, prevent people from creating extents in a
tablespace, you might just get away with it. If people are allowed to
create tables/indexes then you no longer have any control - I know, I've
been there. I've lost coun't of the times I've set up a DMT with nicely
sized objects only to have some numptie come along and create a table
with a wierd set of extent sizes - instantly screwing up all my work.
With DMTs you cannot prevent it I'm afraid.
Also, see my previous comments on importing a dump where the export
defaulted, or explicitly set the COMPRESS parameter to yes - that really
annoys me !
>> Again, how much better LMT is depends on how well or poorly DMT is
sized.
Initially, true, but as I said above, as long as you have users able to
create stuff, you are bolloxed :o(
>>I have used DMT for many years and worked just great.
So have I because I had no choice, now I control the sizes of every
extent in my tablespaces. No-one can create extents that do not confirm
exactly to how I create the tablespace. In otherwords, I'm doing with my
LMTs exactly what you are trying to do with your DMTs but I don't have
to worry about imports or people creating things !
>> I have heard of that approach before. It will probably result in more
>> tablespaces. Let's say if I have 10 tablespaces for tables and 10 for
indexes. If I
>> divide each one into 3, now I have 30 for tables and 30 for indexes.
To enforce this
>> approach, I have to move objects back and forth between tablespaces
as their sizes
>> change. It just gets worse if I further divide the tablespaces.
You need a maximum (?) of 4 tablespaces. (or 8 if you want to separate
out your indexes as well for maintenace purposes, not performance !) See
the document on 'how to stop defragmenting and start living' which
explains it much better than I can. It's on Metalink at
http://support.oracle.co.uk/cgi-bin/cr/getfile_cr.cgi?239049 - reading
and understanding this can seriously relieve your workload. And it was
written for DMTs !
Regards,
Norm.