Re: Maxextents unlimited on LMT ?

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; Received on Wed Jul 03 2002 - 10:55:33 CDT