Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Maxextents unlimited on LMT ?

Re: Maxextents unlimited on LMT ?

From: Pete Sharman <>
Date: 8 Jul 2002 13:49:05 -0700
Message-ID: <>

In article <Ek6W8.30168$>, "Richard says...
>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? ;)


>"Daniel Morgan" <> wrote in message
>> Pete Sharman wrote:
>> > In article <p0BU8.26720$>, "Richard
>> > >
>> > >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
>> > >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
>> > >artificially lowered I generally hear is the argument.
>> > >
>> > >However maxextents was not set solely for performance reasons, another
>> > >reason is space management and capacity management reasons. I might
>> > >well wish to set a table with maxextents to (say 50) not because of
>> > >over performance but concerns that I might not have sufficient space
>> > >over 50 extents. In cooperation with the developers and users, I'm
>> > >x amount of space for this particular object (and indeed all other
>> > >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
>> > >madly, I may well want the object to stop extending rather than fill
>> > >tablespace. I have a rollback segment with a blocking transaction, I
>> > >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
>> > suspect the answer may well be manageability. If you put a limit on the
>> > 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
>> > 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
>> > 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
>> 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.


SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Mon Jul 08 2002 - 15:49:05 CDT

Original text of this message