Maxextents unlimited on LMT ?

Maxextents unlimited on LMT ?

Post by Richard Foot » Thu, 04 Jul 2002 20:21:28



Hi all

I have a question for which I've never received an acceptable answer and for
which I would love someone else's opinion.

Oracle insists that all objects that live in a locally managed tablespace
have maxextents set to unlimited. I guess the question is why ?

I understand the space management advantages of using LMTs and that an
object can quite possibly have many 1000s of extents with minimal to no
performance issues. As performance isn't effected, why then have maxextents
artificially lowered I generally hear is the argument.

However maxextents was not set solely for performance reasons, another key
reason is space management and capacity management reasons. I might very
well wish to set a table with maxextents to (say 50) not because of concerns
over performance but concerns that I might not have sufficient space for
over 50 extents. In cooperation with the developers and users, I'm expecting
x amount of space for this particular object (and indeed all other objects
in a tablespace). If we have grossly underestimated the space by a factor of
x, I may very well want an object to stop extending until the issue is
resolved. I might have an application going ballistic and inserting data
madly, I may well want the object to stop extending rather than fill the
tablespace. I have a rollback segment with a blocking transaction, I may
well want it to stop it extending rather than fill the tablespace. Etc.

However, with a locally managed tablespace I have no such control. The
maxextents storage clause is ignored and I'm not terribly sure why.

Any suggestions ?

Richard

 
 
 

Maxextents unlimited on LMT ?

Post by Jonathan Lewi » Thu, 04 Jul 2002 21:27:07


I hadn't noticed this before.

How about 'designer oversight' as an answer.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November
http://www.jlcomp.demon.co.uk/seminar.html


>Hi all

>Oracle insists that all objects that live in a locally managed tablespace
>have maxextents set to unlimited. I guess the question is why ?


 
 
 

Maxextents unlimited on LMT ?

Post by Pete Sharma » Fri, 05 Jul 2002 00:55:33



Quote:

>Hi all

>I have a question for which I've never received an acceptable answer and for
>which I would love someone else's opinion.

>Oracle insists that all objects that live in a locally managed tablespace
>have maxextents set to unlimited. I guess the question is why ?

>I understand the space management advantages of using LMTs and that an
>object can quite possibly have many 1000s of extents with minimal to no
>performance issues. As performance isn't effected, why then have maxextents
>artificially lowered I generally hear is the argument.

>However maxextents was not set solely for performance reasons, another key
>reason is space management and capacity management reasons. I might very
>well wish to set a table with maxextents to (say 50) not because of concerns
>over performance but concerns that I might not have sufficient space for
>over 50 extents. In cooperation with the developers and users, I'm expecting
>x amount of space for this particular object (and indeed all other objects
>in a tablespace). If we have grossly underestimated the space by a factor of
>x, I may very well want an object to stop extending until the issue is
>resolved. I might have an application going ballistic and inserting data
>madly, I may well want the object to stop extending rather than fill the
>tablespace. I have a rollback segment with a blocking transaction, I may
>well want it to stop it extending rather than fill the tablespace. Etc.

>However, with a locally managed tablespace I have no such control. The
>maxextents storage clause is ignored and I'm not terribly sure why.

>Any suggestions ?

>Richard

Hey BD,

Not sure what the real answer is on this (you'd have to ask the actual
developer) but based on some discussions I've had with product managers I
suspect the answer may well be manageability.  If you put a limit on the number
of extents, you need to manage what happens when you run out of them.  One of
the pushes in the technology I've seen over the past couple of releases is to
make Oracle easier to manage.  So when you put in new technology, put in
defaults that mean you shouldn't need to interfere with it.

Now that's not saying there are never cases when you need to interfere with it.
In fact, you've given some very good examples of that.  Unfortunately, what I'm
seeing (and it would be interesting to hear from others on whether they see this
too) is that more and more Oracle installations do not have full-time skilled
DBA's.  As a result, some of the things that those of us who have been in the
DBA arena for years would normally do (like capacity planning) are simply not
done because no-one knows how to do it.  So what Oracle is attempting to do with
each release is idiot-proof the database but still allow experienced people to
override the defaults.  In this case, it looks like that final step is missing,
though if you concerned about it you can probably get around it in a rough and
ready way by distribution of objects in relevantly sized tablespaces.

Again, this is only what I THINK may be the reasoning, so I could be miles out!

HTH.  Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements;

 
 
 

Maxextents unlimited on LMT ?

Post by Daniel Morga » Fri, 05 Jul 2002 04:33:07




> >Hi all

> >I have a question for which I've never received an acceptable answer and for
> >which I would love someone else's opinion.

> >Oracle insists that all objects that live in a locally managed tablespace
> >have maxextents set to unlimited. I guess the question is why ?

> >I understand the space management advantages of using LMTs and that an
> >object can quite possibly have many 1000s of extents with minimal to no
> >performance issues. As performance isn't effected, why then have maxextents
> >artificially lowered I generally hear is the argument.

> >However maxextents was not set solely for performance reasons, another key
> >reason is space management and capacity management reasons. I might very
> >well wish to set a table with maxextents to (say 50) not because of concerns
> >over performance but concerns that I might not have sufficient space for
> >over 50 extents. In cooperation with the developers and users, I'm expecting
> >x amount of space for this particular object (and indeed all other objects
> >in a tablespace). If we have grossly underestimated the space by a factor of
> >x, I may very well want an object to stop extending until the issue is
> >resolved. I might have an application going ballistic and inserting data
> >madly, I may well want the object to stop extending rather than fill the
> >tablespace. I have a rollback segment with a blocking transaction, I may
> >well want it to stop it extending rather than fill the tablespace. Etc.

> >However, with a locally managed tablespace I have no such control. The
> >maxextents storage clause is ignored and I'm not terribly sure why.

> >Any suggestions ?

> >Richard

> Hey BD,

> Not sure what the real answer is on this (you'd have to ask the actual
> developer) but based on some discussions I've had with product managers I
> suspect the answer may well be manageability.  If you put a limit on the number
> of extents, you need to manage what happens when you run out of them.  One of
> the pushes in the technology I've seen over the past couple of releases is to
> make Oracle easier to manage.  So when you put in new technology, put in
> defaults that mean you shouldn't need to interfere with it.

> Now that's not saying there are never cases when you need to interfere with it.
> In fact, you've given some very good examples of that.  Unfortunately, what I'm
> seeing (and it would be interesting to hear from others on whether they see this
> too) is that more and more Oracle installations do not have full-time skilled
> DBA's.  As a result, some of the things that those of us who have been in the
> DBA arena for years would normally do (like capacity planning) are simply not
> done because no-one knows how to do it.  So what Oracle is attempting to do with
> each release is idiot-proof the database but still allow experienced people to
> override the defaults.  In this case, it looks like that final step is missing,
> though if you concerned about it you can probably get around it in a rough and
> ready way by distribution of objects in relevantly sized tablespaces.

> Again, this is only what I THINK may be the reasoning, so I could be miles out!

> HTH.  Additions and corrections welcome.

> Pete

> SELECT standard_disclaimer, witty_remark FROM company_requirements;

That may well be it. But if one wishes to take that attitude toward managing a
database they will likely do the same thing with respect to disk I/O, pool sizing,
and all of the other parameters that lazy people are free to ignore.

I'd rather set a limit and have the production DBAs paying attention.

Daniel Morgan

 
 
 

Maxextents unlimited on LMT ?

Post by Connor McDonal » Fri, 05 Jul 2002 06:58:31



> Hi all

> I have a question for which I've never received an acceptable answer and for
> which I would love someone else's opinion.

> Oracle insists that all objects that live in a locally managed tablespace
> have maxextents set to unlimited. I guess the question is why ?

> I understand the space management advantages of using LMTs and that an
> object can quite possibly have many 1000s of extents with minimal to no
> performance issues. As performance isn't effected, why then have maxextents
> artificially lowered I generally hear is the argument.

> However maxextents was not set solely for performance reasons, another key
> reason is space management and capacity management reasons. I might very
> well wish to set a table with maxextents to (say 50) not because of concerns
> over performance but concerns that I might not have sufficient space for
> over 50 extents. In cooperation with the developers and users, I'm expecting
> x amount of space for this particular object (and indeed all other objects
> in a tablespace). If we have grossly underestimated the space by a factor of
> x, I may very well want an object to stop extending until the issue is
> resolved. I might have an application going ballistic and inserting data
> madly, I may well want the object to stop extending rather than fill the
> tablespace. I have a rollback segment with a blocking transaction, I may
> well want it to stop it extending rather than fill the tablespace. Etc.

> However, with a locally managed tablespace I have no such control. The
> maxextents storage clause is ignored and I'm not terribly sure why.

> Any suggestions ?

> Richard

I would say that the closest approximateion you could get would be
tablespace quotas, but of course that would typically require lots of
forethought and lots of tablespaces to get close to controlling this.

hth
connor
--
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."

 
 
 

Maxextents unlimited on LMT ?

Post by Linda L » Sat, 06 Jul 2002 02:28:00


What's the difference between MAXSIZE on datafile and maxextents
storage clause?
I used MAXSIZE when created LMT tablespace. I assumed that it will put
some limits on the space growth, and if the datafile reaches the
MAXSIZE I have to add more datafile to the tablespace or use 'alter
... resize;'. Am I right?

Thanks a lot.

 
 
 

Maxextents unlimited on LMT ?

Post by Richard Foot » Sat, 06 Jul 2002 08:23:38


Hi Linda,

A lot (like the difference in talent between David Bowie and my next door
neighbour singing in the shower).

You're right regarding MAXSIZE. If you have autoextend on on the datafiles,
it will grow to the size of MAXSIZE.

MAXEXTENTS is completely different. It controls the maximum number of
extents an object (eg. table) living in the tablespace can grow to. In a
Locally Managed Tablespace this is automatically set for all objects,
regardless of any storage clause to unlimited. So any object can pretty well
grow to any size within the tablespace and you can't control it otherwise.
In my humble opinion, a bit a drawback and an unfortunate loss of admin
control.

Hope it make sense.

Cheers

Richard

Quote:> What's the difference between MAXSIZE on datafile and maxextents
> storage clause?
> I used MAXSIZE when created LMT tablespace. I assumed that it will put
> some limits on the space growth, and if the datafile reaches the
> MAXSIZE I have to add more datafile to the tablespace or use 'alter
> ... resize;'. Am I right?

> Thanks a lot.

 
 
 

Maxextents unlimited on LMT ?

Post by Richard Foot » Tue, 09 Jul 2002 10:53:27


Hi Jonathan,

As good a reason as I've heard !!

Regards

Richard

> I hadn't noticed this before.

> How about 'designer oversight' as an answer.

> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk

> Next Seminars
>         UK            July / Sept
>         Australia      July / August
>         Malaysia        September
>         USA (MI)        November
> http://www.jlcomp.demon.co.uk/seminar.html


> >Hi all

> >Oracle insists that all objects that live in a locally managed tablespace
> >have maxextents set to unlimited. I guess the question is why ?

 
 
 

Maxextents unlimited on LMT ?

Post by Richard Foot » Tue, 09 Jul 2002 11:04:53


I agree with your sentiments.

Not convinced that Pete's reason is it though.

Regards

Richard




says...

> > >Hi all

> > >I have a question for which I've never received an acceptable answer
and for
> > >which I would love someone else's opinion.

> > >Oracle insists that all objects that live in a locally managed
tablespace
> > >have maxextents set to unlimited. I guess the question is why ?

> > >I understand the space management advantages of using LMTs and that an
> > >object can quite possibly have many 1000s of extents with minimal to no
> > >performance issues. As performance isn't effected, why then have
maxextents
> > >artificially lowered I generally hear is the argument.

> > >However maxextents was not set solely for performance reasons, another
key
> > >reason is space management and capacity management reasons. I might
very
> > >well wish to set a table with maxextents to (say 50) not because of
concerns
> > >over performance but concerns that I might not have sufficient space
for
> > >over 50 extents. In cooperation with the developers and users, I'm
expecting
> > >x amount of space for this particular object (and indeed all other
objects
> > >in a tablespace). If we have grossly underestimated the space by a
factor of
> > >x, I may very well want an object to stop extending until the issue is
> > >resolved. I might have an application going ballistic and inserting
data
> > >madly, I may well want the object to stop extending rather than fill
the
> > >tablespace. I have a rollback segment with a blocking transaction, I
may
> > >well want it to stop it extending rather than fill the tablespace. Etc.

> > >However, with a locally managed tablespace I have no such control. The
> > >maxextents storage clause is ignored and I'm not terribly sure why.

> > >Any suggestions ?

> > >Richard

> > Hey BD,

> > Not sure what the real answer is on this (you'd have to ask the actual
> > developer) but based on some discussions I've had with product managers
I
> > suspect the answer may well be manageability.  If you put a limit on the
number
> > of extents, you need to manage what happens when you run out of them.
One of
> > the pushes in the technology I've seen over the past couple of releases
is to
> > make Oracle easier to manage.  So when you put in new technology, put in
> > defaults that mean you shouldn't need to interfere with it.

> > Now that's not saying there are never cases when you need to interfere
with it.
> > In fact, you've given some very good examples of that.  Unfortunately,
what I'm
> > seeing (and it would be interesting to hear from others on whether they
see this
> > too) is that more and more Oracle installations do not have full-time
skilled
> > DBA's.  As a result, some of the things that those of us who have been
in the
> > DBA arena for years would normally do (like capacity planning) are
simply not
> > done because no-one knows how to do it.  So what Oracle is attempting to
do with
> > each release is idiot-proof the database but still allow experienced
people to
> > override the defaults.  In this case, it looks like that final step is
missing,
> > though if you concerned about it you can probably get around it in a
rough and
> > ready way by distribution of objects in relevantly sized tablespaces.

> > Again, this is only what I THINK may be the reasoning, so I could be
miles out!

> > HTH.  Additions and corrections welcome.

> > Pete

> > SELECT standard_disclaimer, witty_remark FROM company_requirements;

> That may well be it. But if one wishes to take that attitude toward
managing a
> database they will likely do the same thing with respect to disk I/O, pool
sizing,
> and all of the other parameters that lazy people are free to ignore.

> I'd rather set a limit and have the production DBAs paying attention.

> Daniel Morgan

 
 
 

Maxextents unlimited on LMT ?

Post by Pete Sharma » Wed, 10 Jul 2002 05:49:05



Quote:

>I agree with your sentiments.

>Not convinced that Pete's reason is it though.

Oh ye of little faith!  Though knowing Richard I'm not surprised!  ;)

OK, since you won't believe me, I spoke to the relevant Development lead, and
here's what he said (word for word):

"Your assumption [as I stated in my original reply to Richard] is accurate. The
number of extents in a locally managed tablespace has absolutely none (very
negligible in the worst of cases) impact on performance. As such there is no
need to set this parameter which puts an artificial limit on the size of an
object and hence, requires additional manual intervention.

What exactly is your client not convinced about? Do they find the argument of
number of extent having no performance impact unacceptable or do they have a
need to control the object size?"

I've emailed them your OP, Richard, so it will be interesting to see if they
have a specific viewpoint on the scenarios you gave.

Oh, and now do you believe me?  ;)

Pete

>Regards

>Richard





>says...

>> > >Hi all

>> > >I have a question for which I've never received an acceptable answer
>and for
>> > >which I would love someone else's opinion.

>> > >Oracle insists that all objects that live in a locally managed
>tablespace
>> > >have maxextents set to unlimited. I guess the question is why ?

>> > >I understand the space management advantages of using LMTs and that an
>> > >object can quite possibly have many 1000s of extents with minimal to no
>> > >performance issues. As performance isn't effected, why then have
>maxextents
>> > >artificially lowered I generally hear is the argument.

>> > >However maxextents was not set solely for performance reasons, another
>key
>> > >reason is space management and capacity management reasons. I might
>very
>> > >well wish to set a table with maxextents to (say 50) not because of
>concerns
>> > >over performance but concerns that I might not have sufficient space
>for
>> > >over 50 extents. In cooperation with the developers and users, I'm
>expecting
>> > >x amount of space for this particular object (and indeed all other
>objects
>> > >in a tablespace). If we have grossly underestimated the space by a
>factor of
>> > >x, I may very well want an object to stop extending until the issue is
>> > >resolved. I might have an application going ballistic and inserting
>data
>> > >madly, I may well want the object to stop extending rather than fill
>the
>> > >tablespace. I have a rollback segment with a blocking transaction, I
>may
>> > >well want it to stop it extending rather than fill the tablespace. Etc.

>> > >However, with a locally managed tablespace I have no such control. The
>> > >maxextents storage clause is ignored and I'm not terribly sure why.

>> > >Any suggestions ?

>> > >Richard

>> > Hey BD,

>> > Not sure what the real answer is on this (you'd have to ask the actual
>> > developer) but based on some discussions I've had with product managers
>I
>> > suspect the answer may well be manageability.  If you put a limit on the
>number
>> > of extents, you need to manage what happens when you run out of them.
>One of
>> > the pushes in the technology I've seen over the past couple of releases
>is to
>> > make Oracle easier to manage.  So when you put in new technology, put in
>> > defaults that mean you shouldn't need to interfere with it.

>> > Now that's not saying there are never cases when you need to interfere
>with it.
>> > In fact, you've given some very good examples of that.  Unfortunately,
>what I'm
>> > seeing (and it would be interesting to hear from others on whether they
>see this
>> > too) is that more and more Oracle installations do not have full-time
>skilled
>> > DBA's.  As a result, some of the things that those of us who have been
>in the
>> > DBA arena for years would normally do (like capacity planning) are
>simply not
>> > done because no-one knows how to do it.  So what Oracle is attempting to
>do with
>> > each release is idiot-proof the database but still allow experienced
>people to
>> > override the defaults.  In this case, it looks like that final step is
>missing,
>> > though if you concerned about it you can probably get around it in a
>rough and
>> > ready way by distribution of objects in relevantly sized tablespaces.

>> > Again, this is only what I THINK may be the reasoning, so I could be
>miles out!

>> > HTH.  Additions and corrections welcome.

>> > Pete

>> > SELECT standard_disclaimer, witty_remark FROM company_requirements;

>> That may well be it. But if one wishes to take that attitude toward
>managing a
>> database they will likely do the same thing with respect to disk I/O, pool
>sizing,
>> and all of the other parameters that lazy people are free to ignore.

>> I'd rather set a limit and have the production DBAs paying attention.

>> Daniel Morgan

HTH.  Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements;

 
 
 

Maxextents unlimited on LMT ?

Post by Richard Foot » Wed, 10 Jul 2002 12:57:45


Hi Pete,

Every time I doubt you, every time I think you've been taking one too many
back pain relievers, every time I think you've been talking to Marcel for
too long, you come back, prove me wrong and re-instil my trust in you :)

Many thanks for chasing this up with the developers.

Now go back and tell them that they're wrong, that there are quite
legitimate reasons why we may want to control the maximum size of an object
and gives us (the option) of having some kind of control again.

I look forward to hearing from them.

Ta again !!

Richard



> >I agree with your sentiments.

> >Not convinced that Pete's reason is it though.

> Oh ye of little faith!  Though knowing Richard I'm not surprised!  ;)

> OK, since you won't believe me, I spoke to the relevant Development lead,
and
> here's what he said (word for word):

> "Your assumption [as I stated in my original reply to Richard] is
accurate. The
> number of extents in a locally managed tablespace has absolutely none
(very
> negligible in the worst of cases) impact on performance. As such there is
no
> need to set this parameter which puts an artificial limit on the size of
an
> object and hence, requires additional manual intervention.

> What exactly is your client not convinced about? Do they find the argument
of
> number of extent having no performance impact unacceptable or do they have
a
> need to control the object size?"

> I've emailed them your OP, Richard, so it will be interesting to see if
they
> have a specific viewpoint on the scenarios you gave.

> Oh, and now do you believe me?  ;)

> Pete

> >Regards

> >Richard





> >says...

> >> > >Hi all

> >> > >I have a question for which I've never received an acceptable answer
> >and for
> >> > >which I would love someone else's opinion.

> >> > >Oracle insists that all objects that live in a locally managed
> >tablespace
> >> > >have maxextents set to unlimited. I guess the question is why ?

> >> > >I understand the space management advantages of using LMTs and that
an
> >> > >object can quite possibly have many 1000s of extents with minimal to
no
> >> > >performance issues. As performance isn't effected, why then have
> >maxextents
> >> > >artificially lowered I generally hear is the argument.

> >> > >However maxextents was not set solely for performance reasons,
another
> >key
> >> > >reason is space management and capacity management reasons. I might
> >very
> >> > >well wish to set a table with maxextents to (say 50) not because of
> >concerns
> >> > >over performance but concerns that I might not have sufficient space
> >for
> >> > >over 50 extents. In cooperation with the developers and users, I'm
> >expecting
> >> > >x amount of space for this particular object (and indeed all other
> >objects
> >> > >in a tablespace). If we have grossly underestimated the space by a
> >factor of
> >> > >x, I may very well want an object to stop extending until the issue
is
> >> > >resolved. I might have an application going ballistic and inserting
> >data
> >> > >madly, I may well want the object to stop extending rather than fill
> >the
> >> > >tablespace. I have a rollback segment with a blocking transaction, I
> >may
> >> > >well want it to stop it extending rather than fill the tablespace.
Etc.

> >> > >However, with a locally managed tablespace I have no such control.
The
> >> > >maxextents storage clause is ignored and I'm not terribly sure why.

> >> > >Any suggestions ?

> >> > >Richard

> >> > Hey BD,

> >> > Not sure what the real answer is on this (you'd have to ask the
actual
> >> > developer) but based on some discussions I've had with product
managers
> >I
> >> > suspect the answer may well be manageability.  If you put a limit on
the
> >number
> >> > of extents, you need to manage what happens when you run out of them.
> >One of
> >> > the pushes in the technology I've seen over the past couple of
releases
> >is to
> >> > make Oracle easier to manage.  So when you put in new technology, put
in
> >> > defaults that mean you shouldn't need to interfere with it.

> >> > Now that's not saying there are never cases when you need to
interfere
> >with it.
> >> > In fact, you've given some very good examples of that.
Unfortunately,
> >what I'm
> >> > seeing (and it would be interesting to hear from others on whether
they
> >see this
> >> > too) is that more and more Oracle installations do not have full-time
> >skilled
> >> > DBA's.  As a result, some of the things that those of us who have
been
> >in the
> >> > DBA arena for years would normally do (like capacity planning) are
> >simply not
> >> > done because no-one knows how to do it.  So what Oracle is attempting
to
> >do with
> >> > each release is idiot-proof the database but still allow experienced
> >people to
> >> > override the defaults.  In this case, it looks like that final step
is
> >missing,
> >> > though if you concerned about it you can probably get around it in a
> >rough and
> >> > ready way by distribution of objects in relevantly sized tablespaces.

> >> > Again, this is only what I THINK may be the reasoning, so I could be
> >miles out!

> >> > HTH.  Additions and corrections welcome.

> >> > Pete

> >> > SELECT standard_disclaimer, witty_remark FROM company_requirements;

> >> That may well be it. But if one wishes to take that attitude toward
> >managing a
> >> database they will likely do the same thing with respect to disk I/O,
pool
> >sizing,
> >> and all of the other parameters that lazy people are free to ignore.

> >> I'd rather set a limit and have the production DBAs paying attention.

> >> Daniel Morgan

> HTH.  Additions and corrections welcome.

> Pete

> SELECT standard_disclaimer, witty_remark FROM company_requirements;

 
 
 

Maxextents unlimited on LMT ?

Post by Pete Sharma » Wed, 10 Jul 2002 13:53:48



Quote:

>Hi Pete,

>Every time I doubt you, every time I think you've been taking one too many
>back pain relievers, every time I think you've been talking to Marcel for
>too long, you come back, prove me wrong and re-instil my trust in you :)

>Many thanks for chasing this up with the developers.

>Now go back and tell them that they're wrong, that there are quite
>legitimate reasons why we may want to control the maximum size of an object
>and gives us (the option) of having some kind of control again.

>I look forward to hearing from them.

Well you never know what might happen in future releases!  :)

I agree there are a number of reasons for this, and it makes sense to me to be
able to have an object MAXSIZE parameter, but I really have no idea how
difficult this would be to include in the code (for some reason Oracle wants to
limit who has source code access, and I ain't one of them!)  However, it's been
put forward as a possible enhancement request (which may have more weight if
you, a client now, actually log it).  Of course, I have absolutely no decision
making powers in this arena, so who knows if it will make it into the code.

BTW, does Marcel ever read this NG or is he really a developer now?  ;)

(Note to other readers - this is a dig at Marcel, not developers per se!)

Pete

>Ta again !!

>Richard




>> >I agree with your sentiments.

>> >Not convinced that Pete's reason is it though.

>> Oh ye of little faith!  Though knowing Richard I'm not surprised!  ;)

>> OK, since you won't believe me, I spoke to the relevant Development lead,
>and
>> here's what he said (word for word):

>> "Your assumption [as I stated in my original reply to Richard] is
>accurate. The
>> number of extents in a locally managed tablespace has absolutely none
>(very
>> negligible in the worst of cases) impact on performance. As such there is
>no
>> need to set this parameter which puts an artificial limit on the size of
>an
>> object and hence, requires additional manual intervention.

>> What exactly is your client not convinced about? Do they find the argument
>of
>> number of extent having no performance impact unacceptable or do they have
>a
>> need to control the object size?"

>> I've emailed them your OP, Richard, so it will be interesting to see if
>they
>> have a specific viewpoint on the scenarios you gave.

>> Oh, and now do you believe me?  ;)

>> Pete

>> >Regards

>> >Richard





>> >says...

>> >> > >Hi all

>> >> > >I have a question for which I've never received an acceptable answer
>> >and for
>> >> > >which I would love someone else's opinion.

>> >> > >Oracle insists that all objects that live in a locally managed
>> >tablespace
>> >> > >have maxextents set to unlimited. I guess the question is why ?

>> >> > >I understand the space management advantages of using LMTs and that
>an
>> >> > >object can quite possibly have many 1000s of extents with minimal to
>no
>> >> > >performance issues. As performance isn't effected, why then have
>> >maxextents
>> >> > >artificially lowered I generally hear is the argument.

>> >> > >However maxextents was not set solely for performance reasons,
>another
>> >key
>> >> > >reason is space management and capacity management reasons. I might
>> >very
>> >> > >well wish to set a table with maxextents to (say 50) not because of
>> >concerns
>> >> > >over performance but concerns that I might not have sufficient space
>> >for
>> >> > >over 50 extents. In cooperation with the developers and users, I'm
>> >expecting
>> >> > >x amount of space for this particular object (and indeed all other
>> >objects
>> >> > >in a tablespace). If we have grossly underestimated the space by a
>> >factor of
>> >> > >x, I may very well want an object to stop extending until the issue
>is
>> >> > >resolved. I might have an application going ballistic and inserting
>> >data
>> >> > >madly, I may well want the object to stop extending rather than fill
>> >the
>> >> > >tablespace. I have a rollback segment with a blocking transaction, I
>> >may
>> >> > >well want it to stop it extending rather than fill the tablespace.
>Etc.

>> >> > >However, with a locally managed tablespace I have no such control.
>The
>> >> > >maxextents storage clause is ignored and I'm not terribly sure why.

>> >> > >Any suggestions ?

>> >> > >Richard

>> >> > Hey BD,

>> >> > Not sure what the real answer is on this (you'd have to ask the
>actual
>> >> > developer) but based on some discussions I've had with product
>managers
>> >I
>> >> > suspect the answer may well be manageability.  If you put a limit on
>the
>> >number
>> >> > of extents, you need to manage what happens when you run out of them.
>> >One of
>> >> > the pushes in the technology I've seen over the past couple of
>releases
>> >is to
>> >> > make Oracle easier to manage.  So when you put in new technology, put
>in
>> >> > defaults that mean you shouldn't need to interfere with it.

>> >> > Now that's not saying there are never cases when you need to
>interfere
>> >with it.
>> >> > In fact, you've given some very good examples of that.
>Unfortunately,
>> >what I'm
>> >> > seeing (and it would be interesting to hear from others on whether
>they
>> >see this
>> >> > too) is that more and more Oracle installations do not have full-time
>> >skilled
>> >> > DBA's.  As a result, some of the things that those of us who have
>been
>> >in the
>> >> > DBA arena for years would normally do (like capacity planning) are
>> >simply not
>> >> > done because no-one knows how to do it.  So what Oracle is attempting
>to
>> >do with
>> >> > each release is idiot-proof the database but still allow experienced
>> >people to
>> >> > override the defaults.  In this case, it looks like that final step
>is
>> >missing,
>> >> > though if you concerned about it you can probably get around it in a
>> >rough and
>> >> > ready way by distribution of objects in relevantly sized tablespaces.

>> >> > Again, this is only what I THINK may be the reasoning, so I could be
>> >miles out!

>> >> > HTH.  Additions and corrections welcome.

>> >> > Pete

>> >> > SELECT standard_disclaimer, witty_remark FROM company_requirements;

>> >> That may well be it. But if one wishes to take that attitude toward
>> >managing a
>> >> database they will likely do the same thing with respect to disk I/O,
>pool
>> >sizing,
>> >> and all of the other parameters that lazy people are free to ignore.

>> >> I'd rather set a limit and have the production DBAs paying attention.

>> >> Daniel Morgan

>> HTH.  Additions and corrections welcome.

>> Pete

>> SELECT standard_disclaimer, witty_remark FROM company_requirements;

HTH.  Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements;

 
 
 

Maxextents unlimited on LMT ?

Post by Connor McDonal » Thu, 11 Jul 2002 04:08:51




> >I agree with your sentiments.

> >Not convinced that Pete's reason is it though.

> Oh ye of little faith!  Though knowing Richard I'm not surprised!  ;)

> OK, since you won't believe me, I spoke to the relevant Development lead, and
> here's what he said (word for word):

> "Your assumption [as I stated in my original reply to Richard] is accurate. The
> number of extents in a locally managed tablespace has absolutely none (very
> negligible in the worst of cases) impact on performance. As such there is no
> need to set this parameter which puts an artificial limit on the size of an
> object and hence, requires additional manual intervention.

> What exactly is your client not convinced about? Do they find the argument of
> number of extent having no performance impact unacceptable or do they have a
> need to control the object size?"

> I've emailed them your OP, Richard, so it will be interesting to see if they
> have a specific viewpoint on the scenarios you gave.

> Oh, and now do you believe me?  ;)

> Pete

> >Regards

> >Richard





> >says...

> >> > >Hi all

> >> > >I have a question for which I've never received an acceptable answer
> >and for
> >> > >which I would love someone else's opinion.

> >> > >Oracle insists that all objects that live in a locally managed
> >tablespace
> >> > >have maxextents set to unlimited. I guess the question is why ?

> >> > >I understand the space management advantages of using LMTs and that an
> >> > >object can quite possibly have many 1000s of extents with minimal to no
> >> > >performance issues. As performance isn't effected, why then have
> >maxextents
> >> > >artificially lowered I generally hear is the argument.

> >> > >However maxextents was not set solely for performance reasons, another
> >key
> >> > >reason is space management and capacity management reasons. I might
> >very
> >> > >well wish to set a table with maxextents to (say 50) not because of
> >concerns
> >> > >over performance but concerns that I might not have sufficient space
> >for
> >> > >over 50 extents. In cooperation with the developers and users, I'm
> >expecting
> >> > >x amount of space for this particular object (and indeed all other
> >objects
> >> > >in a tablespace). If we have grossly underestimated the space by a
> >factor of
> >> > >x, I may very well want an object to stop extending until the issue is
> >> > >resolved. I might have an application going ballistic and inserting
> >data
> >> > >madly, I may well want the object to stop extending rather than fill
> >the
> >> > >tablespace. I have a rollback segment with a blocking transaction, I
> >may
> >> > >well want it to stop it extending rather than fill the tablespace. Etc.

> >> > >However, with a locally managed tablespace I have no such control. The
> >> > >maxextents storage clause is ignored and I'm not terribly sure why.

> >> > >Any suggestions ?

> >> > >Richard

> >> > Hey BD,

> >> > Not sure what the real answer is on this (you'd have to ask the actual
> >> > developer) but based on some discussions I've had with product managers
> >I
> >> > suspect the answer may well be manageability.  If you put a limit on the
> >number
> >> > of extents, you need to manage what happens when you run out of them.
> >One of
> >> > the pushes in the technology I've seen over the past couple of releases
> >is to
> >> > make Oracle easier to manage.  So when you put in new technology, put in
> >> > defaults that mean you shouldn't need to interfere with it.

> >> > Now that's not saying there are never cases when you need to interfere
> >with it.
> >> > In fact, you've given some very good examples of that.  Unfortunately,
> >what I'm
> >> > seeing (and it would be interesting to hear from others on whether they
> >see this
> >> > too) is that more and more Oracle installations do not have full-time
> >skilled
> >> > DBA's.  As a result, some of the things that those of us who have been
> >in the
> >> > DBA arena for years would normally do (like capacity planning) are
> >simply not
> >> > done because no-one knows how to do it.  So what Oracle is attempting to
> >do with
> >> > each release is idiot-proof the database but still allow experienced
> >people to
> >> > override the defaults.  In this case, it looks like that final step is
> >missing,
> >> > though if you concerned about it you can probably get around it in a
> >rough and
> >> > ready way by distribution of objects in relevantly sized tablespaces.

> >> > Again, this is only what I THINK may be the reasoning, so I could be
> >miles out!

> >> > HTH.  Additions and corrections welcome.

> >> > Pete

> >> > SELECT standard_disclaimer, witty_remark FROM company_requirements;

> >> That may well be it. But if one wishes to take that attitude toward
> >managing a
> >> database they will likely do the same thing with respect to disk I/O, pool
> >sizing,
> >> and all of the other parameters that lazy people are free to ignore.

> >> I'd rather set a limit and have the production DBAs paying attention.

> >> Daniel Morgan

> HTH.  Additions and corrections welcome.

> Pete

> SELECT standard_disclaimer, witty_remark FROM company_requirements;

So the quote was:

"The number of extents in a locally managed tablespace has absolutely
none (very negligible in the worst of cases) impact on performance"

Could you pass this test onto the development lead...

SQL> create tablespace dummy
  2  datafile 'e:\orasw\db1\dummy.dbf' size 2001m
  3  extent management local uniform size 32k;

SQL> create user x identified by x;

User created.

SQL> alter user x quota 1000m on dummy;

User altered.

SQL> set timing on
SQL> create table x.p ( x number ) tablespace dummy
  2  storage ( initial 99m next 99m minextents 10 );

Table created.

Elapsed: 00:02:04.57
SQL> drop table x.p;

Table dropped.

Elapsed: 00:01:92.14

2 MINUTES!!!! WHAT A SHAMBLES!

When they put the quota system in a bitmap as well, then I'll be
convinced :-)

Cheers
Connor
--
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."

 
 
 

Maxextents unlimited on LMT ?

Post by Jonathan Lewi » Thu, 11 Jul 2002 04:25:24


That's the problem with doing proper tests,
they so often produce the wrong results !

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November
http://www.jlcomp.demon.co.uk/seminar.html


>So the quote was:

>"The number of extents in a locally managed tablespace has absolutely
>none (very negligible in the worst of cases) impact on performance"

>Could you pass this test onto the development lead...

>SQL> create tablespace dummy
>  2  datafile 'e:\orasw\db1\dummy.dbf' size 2001m
>  3  extent management local uniform size 32k;

>SQL> create user x identified by x;

>User created.

>SQL> alter user x quota 1000m on dummy;

>User altered.

>SQL> set timing on
>SQL> create table x.p ( x number ) tablespace dummy
>  2  storage ( initial 99m next 99m minextents 10 );

>Table created.

>Elapsed: 00:02:04.57
>SQL> drop table x.p;

>Table dropped.

>Elapsed: 00:01:92.14

>2 MINUTES!!!! WHAT A SHAMBLES!

>When they put the quota system in a bitmap as well, then I'll be
>convinced :-)

>Cheers
>Connor
>--
>==============================
>Connor McDonald

 
 
 

Maxextents unlimited on LMT ?

Post by Pete Sharma » Thu, 11 Jul 2002 05:49:35



snip

>So the quote was:

>"The number of extents in a locally managed tablespace has absolutely
>none (very negligible in the worst of cases) impact on performance"

>Could you pass this test onto the development lead...

>SQL> create tablespace dummy
>  2  datafile 'e:\orasw\db1\dummy.dbf' size 2001m
>  3  extent management local uniform size 32k;

>SQL> create user x identified by x;

>User created.

>SQL> alter user x quota 1000m on dummy;

>User altered.

>SQL> set timing on
>SQL> create table x.p ( x number ) tablespace dummy
>  2  storage ( initial 99m next 99m minextents 10 );

>Table created.

>Elapsed: 00:02:04.57
>SQL> drop table x.p;

>Table dropped.

>Elapsed: 00:01:92.14

>2 MINUTES!!!! WHAT A SHAMBLES!

>When they put the quota system in a bitmap as well, then I'll be
>convinced :-)

>Cheers
>Connor
>--
>==============================
>Connor McDonald

>http://www.oracledba.co.uk

>"Some days you're the pigeon, some days you're the statue..."

Hmm, not sure what you ran this on (maybe you've turned off the FAST=TRUE
parameter!).  Here's what I get on my PC with 9.2:

SQL> create tablespace dummy
  2  datafile 'c:\temp\dummy.dbf' size 2001m
  3  extent management local uniform size 32k;
create tablespace dummy
*
ERROR at line 1:
ORA-01119: error in creating database file 'c:\temp\dummy.dbf'
ORA-27044: unable to write the header block of file
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 112) There is not enough space on the disk.

Damn ran out of space.  Clean up and rerun:

SQL> /

Tablespace created.

SQL> create user x identified by x;

User created.

SQL> alter user x quota 1000m on dummy;

User altered.

SQL> set timing on
SQL> create table x.p ( x number ) tablespace dummy
  2  storage ( initial 99m next 99m minextents 10 );

Table created.

Elapsed: 00:01:08.02
SQL> drop table x.p;

Table dropped.

Elapsed: 00:00:04.07
SQL> drop tablespace dummy including contents and datafiles;

Tablespace dropped.

Note the difference in timings.  What version were you on?

HTH.  Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements;

 
 
 

1. 8I EE on RH 6.0: can't specify MAXEXTENTS UNLIMITED

Surely I'm doing something wrong.  I'm quite new to Oracle, so be
gentle...

I've just installed 8i EE on a fresh RH 6.0 system.  I copied some of
the commands from the scripts
that made my database because I want to create my own tablespace.  For
some reason, the
MAXEXTENTS UNLIMITED that I copied is unacceptable to svrmgrl.  It
complains of an
illegal value.  When I remove that phrase, the commands go through.

Any hints?

--



2. QUOTED_IDENTIFIER

3. Oracle 8i EE on Linux: can't set MAXEXTENTS UNLIMITED.

4. Trigger for Update

5. Pros/Cons of LMT

6. Pick uniVerse Unidata SystemBuilder D3 Ardent Programmers, needed for OH, FL, MD, IL, NY, NH and Nationwide-U.S.-opportunities-(Recruiter)

7. OPTIMAL and LMT's

8. Getting the fields that are part of a PK constraint...

9. Minextents for rbsseg in LMT tablespace

10. Algorithm for calculating extent size in LMT

11. Correct uniform size for temporary LMT ?

12. change uniform size in LMT

13. LMT and Siebel/Peoplesoft?