Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: What is against autoextending datafiles? (was: autoextend = ???

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

From: Jim Gregory <Jim.Gregory_at_ncr.com>
Date: Tue, 10 Apr 2001 10:15:43 -0400
Message-ID: <3ad315a5$1@rpc1284.daytonoh.ncr.com>

Alrighty then!! How about this scenario?

HR production systems are definitely not autoextend. DBA monitors and increases prior to problems.

Team of developers on dev/test are running 3rd party vendors HR upgrade procedures to upgrade to next major release. Vendor tells us "the db will be bigger". We ask, "how much bigger?". They reply, "we have no idea". Soooo, we create the db 1 1/2 times the current production size. Developers run vendors upgrade/conversion processes ( some of which run 12 to 24 hours without a commit until the end ) and wind up running out of space in an index TS or table TS or the TEMP TS or Rollback TS. Ok, we double the space and they try again. Now it blows up with the same error 15 hours into the run instead of 8 hours. Another day's work lost for several developers.

So, I decide to turn on autoextend with upper limits at the filesystem size and, voila, they can get a run to complete without running out space and I can get a handle on how big the new production system is going to have to be when the upgrade/conversion processes are run on it.

I really think that in some cases like this, autoextend can be an excellent tool to use to discover how much space is going to be needed for a target system - especially when your vendor does not have a clue.

For some comparisons: our current production DB is about 35G. So far, the dev/test database under the new version is up to about 80G and will probably get even bigger before all of the testing is complete.

--
Jim Gregory
Principal Consultant for Keane, Inc.
Currently assigned to NCR, Dayton, OH

Opinions are my own and do not reflect those
of my employer or clients
"Kerry Scott" <kerrysco_at_ameritech.net> wrote in message
news:xZYz6.4307$Vj5.39023_at_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 Tue Apr 10 2001 - 09:15:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US