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

From: Kerry Scott <kerrysco_at_ameritech.net>
Date: Sun, 8 Apr 2001 08:41:10 -0400
Message-ID: <xZYz6.4307$Vj5.39023@nntp0.chicago.il.ameritech.net>

How about this scenario: DBA does his job and makes sure tablespaces/datafiles are not close to being full (he monitors the database). If a datafile is close to being full, he adds a new one of equal size. So, all datafiles are a standard size, users don't get errors, performance hits are not incurred, and lastly, you don't have all kinds of different size files growing helter skelter all over the place, causing lots of maintenance headaches in the future.

My two cents

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

>

> --
> Gtrz,

>
> Frank van Bortel
Received on Sun Apr 08 2001 - 07:41:10 CDT