"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<409c2ab0$0$10520$afc38c87_at_news.optusnet.com.au>...
> Mark Bole wrote:
>
> > Howard J. Rogers wrote:
> >
> >> Marck wrote:
> >>
> >>> Hi Gurus,
> >>>
> >>> I have a questions about this error.
> >>>
> >>> I have this conf
> >>>
> >>> CREATE TABLESPACE ts1_data
> >>> DATAFILE '/d00/oradata/ts1_data.dbf' size 118M
> >>> AUTOEXTEND ON NEXT 59M MAXSIZE 236M;
> >>
> >>
> > [...]
> >
> >>
> >> Crikey. Autoextend is a bad idea. But incrementing in steps of 59M and
> >> stopping at 236M is even whackier. This is fine-tuning space
> >> management gone mad (IMHO, natch).
> >>
> > [...]
> >
> >>
> >> With an up-to-date 8i, there's no excuse: dictionary managed
> >> tablespace is ancient history, and you should be using LMT.
> >>
> >> Regards
> >> HJR
> >
> >
> > Amen to LMT.
> >
> > But why do you keep on stating without qualification that "autoextend is
> > a bad idea".
>
> What do you mean "without qualification"? I've said it's fine for
> SYSTEM. And I have repeatedly said "but if you must use it, make sure
> you have NEXT and MAXSIZE set". Those are qualifications.
>
> > I have been using autoextend on many datafiles in many
> > types of database (production, training, test, development) for
> > different applications for a long time and have not had any problems, so
> > I wonder what I'm missing?
>
> If you have autoextend on, *when* does the data file grow in size? When
> a segment needs more space, that's when. Why would a segment need more
> space? Because some poor user is trying to insert into it, that's why.
> Ergo, the data file will extend when a user is trying to perform DML.
>
> Can you begin to see why autoextend is not such a good idea?
>
> If you use autoextend AT ALL, *some* of your DML will encounter waits as
> the autoextension is arranged for you. If you use autoextend without a
> large NEXT, then *a lot* of your DML will encounter those waits.
>
> > This is especially hard to reconcile with the repeat claims by others
> > that "disk is cheap" and you shouldn't be concerned about limiting the
> > size of your UNDO tablespace (which implies you should have AUTOEXTEND
> > on for that one?).
>
> Oh dear. UNDO tablespace should never, ever, ever be autoextend. An
> unnecessarily large undo tablespace is an extremely heavy hitter in
> terms of performance degradation.
>
> > Now, "MAXSIZE UNLIMITED" is a bad idea, of course (and unfortunately
> > that's the default for Oracle-managed files when no SIZE is specified).
> > But how is judicious use of AUTOEXTEND any different than carrying a
> > spare tire in your car or an extra piece of currency tucked away in your
> > wallet?
>
> Because however judiciously you use it, *some* DML will suffer for it.
> Does that mean it is a no-no? No, it means it's a question of costs v.
> benefits. If you don't have time to manage your file space allocations
> proactively and carefully, then autoextend is a very convenient
> alternative. But don't ever pretend that Oracle provides such goodies
> for free: they always come with (usually hidden) costs.
>
> >You don't plan to use it, but it's very convenient to get
> > through the unexpected emergency so you have more time to deal with the
> > underlying problem.
>
> I have used it when I go on holiday. I would rather the database keep
> working, however poorly, than that I get rung up when I'm on the beach.
> See: it's a convenience thing. But there are performance implications
> from having all that convenience.
>
> > I haven't tried resumable statements -- I suppose they are an
> > alternative, but with more drawbacks than AUTOEXTEND (as in, you have to
> > explicitly set them up, and they still don't allow an operation to
> > complete until there is some intervention).
>
> Strangely, I thought that's why we had DBAs, to intervene.
>
> Regards
> HJR
Oh boy, sone great quotes in here, if I follow the Foote/Rogers rules,
for a "Silly Things Experts Say" seminar...I can even attach names to
them...
Mike
Received on Sat May 08 2004 - 09:40:19 CDT
