Re: Maxextents unlimited on LMT ?

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 Received on Wed Jul 03 2002 - 14:33:07 CDT