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

From: Howard J. Rogers <howardjr_at_www.com>
Date: Sat, 7 Apr 2001 09:44:23 +1000
Message-ID: <3ace54f9@news.iprimus.com.au>

"Frank van Bortel" <fbortel_at_home.nl> wrote in message news:3ACE28AA.B6128EB0_at_home.nl...
> Connor McDonald wrote:
> >
> > 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."
> > >

>

> Ok, let's have your version, then (and I happily cut'n paste:)
> "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>..
> No, I can't". "Well,I'll throw an error then"
> (error gets thrown)
> Now the user (Jim) get's in:
> "hey, want's that? John, come and have a look, is that normal?"
> <pause while John reluctantly puts his coffemug down, and strolls
> over to Jim's desk:> "Hmmm, nope, don't think so.... dunno... works for
> me all the time. Did you press the right clikcteclick?"
> Jim:"Dunno - think so. Will try again"
> (OK - read once more from the beginning)
> Jim:"He, John, it still not running." John:"Better call Howard, then,
> he'll
> know what to do". Howard is off to lunch, but gets back to his desk and
> fixes
> the problem in notime. Phones back immedeately. Only 15 minutes lost.
>
> Would that be more efficient?

Of course not. But then, as a good DBA, this guy Howard would never have allowed such a state of affairs to arise in the first place!

> > >
> > > 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.

>

> Strike the pretty - it *is* a requirement, not only to keep from filling
> up volumes,
> but also to avoid 2/4GB barries being crossed.

I think you'll find the operating system (ie, the file system) does a fair job of preventing that on it is own, with or without assistance from a maxsize clause.

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

>

> Connor, are you still coalescing by hand? Don't you use locally managed
> tablespaces?
> No you don't - you use the options that come along. That does in no way
> mean you
> don't know what's going on. It's just the same for autoextending
> datafiles.
> Which have crummy defaults, like so much in Oracle's RDBMS. Which has to
> be used wisely, like so much in Oracle's RDBMS. And with caution, like
> so much in
> Oracle's RDBMS.

For my (really!) last post, those sound like words of wisdom that I'd buy into anytime.

Take care
HJR

>

> --
> Gtrz,

>
> Frank van Bortel
Received on Fri Apr 06 2001 - 18:44:23 CDT