Re: Maxextents unlimited on LMT ?

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

"Pete Sharman" <peter.sharman_at_oracle.com> wrote in message news:agcts10d0h_at_drn.newsguy.com...
> In article <Ek6W8.30168$Hj3.91637_at_newsfeeds.bigpond.com>, "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? ;)
>
> Pete
>
> >
> >Regards
> >
> >Richard
> >
> >"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> >news:3D23514D.1BA8DB3_at_exesolutions.com...
> >> Pete Sharman wrote:
> >>
> >> > In article <p0BU8.26720$Hj3.82039_at_newsfeeds.bigpond.com>, "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;
>
Received on Mon Jul 08 2002 - 22:57:45 CDT