> >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? ;)
> >> > >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;
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;
SQL> alter user x quota 1000m on dummy;
SQL> set timing on
SQL> create table x.p ( x number ) tablespace dummy
2 storage ( initial 99m next 99m minextents 10 );
SQL> drop table x.p;
2 MINUTES!!!! WHAT A SHAMBLES!
When they put the quota system in a bitmap as well, then I'll be
"Some days you're the pigeon, some days you're the statue..."