Re: Autoextend switch

"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
******************************************