Re: Autoextend switch

"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:<3ee97d56$0$11381$cc9e4d1f_at_news.dial.pipex.com>...
> "Gary" <rooty_hill2002_at_yahoo.com.au> wrote in message
> news:171bd226.0306122211.50ec871_at_posting.google.com...
> > Hi!
> >
> > I was told technically it is not a good idea to switch on AUTOEXTEND
> > for all tablespaces except for SYSTEM. Practically as a junior Oracle
> > DBA I am really allured to switch it on for all tablespaces.
> >
> > Could anybody explain to me why AUTOEXTEND will cause fragmentation or
> > I/O contention?
>
> My problems with autoextend are listed below
>
> 1. If something starts growing like topsy for some daft reason how are you
> going to know?
> 2. What happens if you fill the disk?
> 3. There are various bugs relating to datafile resizing on various platforms
> that can seriously harm your database.
>
> Autoextend can't cause fragmentation or IO contention so far as I can see.
> Different extent sizes cause fragmentation and frequently accessed objects
> or datafiles on the same disk cause IO contention. Neither of these depend
> on the datafile being set to autoextend.
>
> Setting the NEXT parameter smaller than the extent size of your objects will
> I believe cause unnecessary IO on at least 8.1.7 (i.e IIRC object requests
> 64mb next extent, autoextend is set NEXT 64k instead of NEXT 64m you will
> get 1024 extensions of the datafile instead of 1.)
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
> ******************************************

Thanks Niall.

I work for 24*7 shop and we've got lots of backend batches. Looks like I have to change the code to let the batches to handle some specific error from Oralce in this case, although I as a Oracle DBA, am supposed to monitor the space usage on daily base by using some statistic packages of my own.

I hope I get it right this time.

Thanks again. Received on Sun Jun 15 2003 - 20:44:31 CDT