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

"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
Received on Wed Apr 04 2001 - 17:08:16 CDT