Re: Maxextents unlimited on LMT ?

Pete Sharman wrote:
>
> 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;

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;

User created.

SQL> alter user x quota 1000m on dummy;

User altered.

SQL> set timing on
SQL> create table x.p ( x number ) tablespace dummy   2 storage ( initial 99m next 99m minextents 10 );

Table created.

Elapsed: 00:02:04.57
SQL> drop table x.p;

Table dropped.

Elapsed: 00:01:92.14
system_at_db1>

2 MINUTES!!!! WHAT A SHAMBLES! When they put the quota system in a bitmap as well, then I'll be convinced :-)

Cheers
Connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."