Re: What is against autoextending datafiles? (was: autoextend = ???

Howard J. Rogers wrote:
>
> "Frank van Bortel" <fbortel_at_home.nl> wrote in message
> news:3ACB844F.2CC41C50_at_home.nl...
> >
> > Please elaborate as to why (proof, please!) autoextending datafiles
> > is a bad idea?
> > (In a highly tuned env, I agree - for civil servants, I tend to use it)
> >
>
> When do you think the data files will want to take advantage of its ability
> to autoextend?
>
> 1. When it's run out of space
> 2. When someone is doing a piece of DML which requires more space
>
> So the autoextension takes place precisely when you *don't* want it to
> happen... at exactly the moment when your User is hoping to do a quick
> insert and get on with something more useful to do. Instead, he has to sit
> there whilst a full-blown conversation takes place in the data dictionary
> along the lines of "I'd like to do an Insert please." "Can't -no free
> space". "Well, can you autoextend?" "Don't know -I'll just check <pause>..
> yes, I can". "Well, can you do so please?" "How much by?" "Oh, I'm a
> 50K-extent segment, so 50K will do fine" "OK. Hang on. <pause whilst disk is
> visited> OK you may proceed". "Cheers."
>
> Something like that, anyway!
>
> In short, autoextend tells me that the DBA can't quite summon up the energy
> to proactively monitor his space usage, which is just sloppy DBAing, and
> that the performance issue for his users is less important to him than his
> own convenience.
>
> I confess, however, to having switched autoextend on for all my datafiles
> when going away for a two week holiday, and turning it off again when I got
> back. I'd rather the application kept running, however poorly, than keeling
> over through lack of space (and my mobile phone going off whilst on the
> beach).
>
> I'd also allow that a DBA looking after 50 databases might not have the time
> to closely monitor space availability in all of them, and in that sort of
> situation, autoextend is perhaps a *necessary* convenience.
>
> But if it is going to be used, it's crucial that there is a 'NEXT' clause
> used, so that the thing doesn't continually try and grab tiny bits of extra
> disk space. Instead it should acquire juicy great chunks each time (I seem
> to recall a 'NEXT 100M' every time I went on holiday). And I'd not abandon
> the entire disk to said files, so a 'MAXSIZE' clause is pretty much a
> requirement.
>
> Regards
> HJR
>
> > --
> > Gtrz,
> >
> > Frank van Bortel

I agree but I've often found autoextend on temp and rollback segment tablespaces to be very useful. Namely, allow them to autoextend up to a known max so that we can minimise the kinds of errors that the user typically won't have a clue on, ie, can't extend temp, can't extend rollback, snapshot too old.

Then each night just before backup, I offline the r'segs, clear the temp tspace, shrink the files back down, and I'm ready for tomorrow...

Other than that, generally I reckon autoextend is like holding a flag saying "I don't know whats happening on my database"...

Cheers
Connor

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

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