Up to 8.0 the number of data files had a potential impact on DBWn performance,
not that it could not be managed with proper tuning. That is no longer an issue
under 8.1, but there is still a small impact at checkpoints and a small impact
on the per process memory usage. However, whether you choose 50 x 2G datafiles
or 100 x 1G datafiles should be decided entirely of the basis of ease of
management, not performance.

I have much stronger opinions on the use of AUTOEXTEND. From a performance point
of view the main objection is that you can end up with a wide variety of
datafile sizes, which can make disk load balancing very difficult or even
impossible. Preserving maximum flexibility for disk load balancing is essential
unless you have a crystal ball, or unless you place all your datafiles on one
big stripe set (which I also dislike).

My other objection is that using AUTOEXTEND is a tacit admission of
incompetence. Anyone whose crystal ball is good enough to guarantee that they
will not need to do any disk load balancing, ought not to be daunted by
monitoring space usage to predict when more space will need to be allocated.


-----Original Message-----
Sent: Tuesday, 29 August 2000 23:25


  We are trying to size our ERP production database which will have about
100Gb with about 10 tables having more than 2Gb data. One of our dbas is
insisting on using the AUTOEXTEND ON on the datafiles with an extent size as
100-200mb with a max limited size of 2g/4g. Also  he is suggesting the size
of each datafile to be 1g against my recommendation of 2g as the uniform
datafile size. I would like to know whether AUTOEXTEND ON  is advisable to
use and are there any overhead in extending the datafile when needed. Also
what effect has it on the database to have 100 1g datafiles against 50 2g
datafiles. Please advise.