Advice Needed -Free space in tablespace for performance

[dba-k]

Could anybody provide some thoughts about the free space inside an Oracle database. Does the
amount of free space inside the tablespace containing all the tables inside an OLTP Production
database affect its performance.

Should there be a threshold for free space on the disk where the datafiles are located. As the SYSTEM
and UNDO tablespaces are on AUTOEXTEND mode, is it possible to find how much space they would swell
on at the worst peak.

And lastly, is it better to increase the datafile size or add another datafile to increase tablespace size.
The database in question is an OLTP Production database on Oracle 9205 & Solaris 5.8

Please do let me know if there is any other info that I missed to provide here.
Thanks in advance...

[davey23uk]

free space doesnt matter, its irrelevant

undo shouldnt be on autoextend, cant tell how much they would 'swell' as they should be fairly static

the thing with datafiles is do whatever keeps your io spread evenly across all your devices

[Telco_DBA]

Should there be a threshold for free space on the disk where the datafiles are located. As the SYSTEM
and UNDO tablespaces are on AUTOEXTEND mode, is it possible to find how much space they would swell
on at the worst peak.

I agree to Davey's statement not to use autoextend. When unusual jobs (yearly journals / batch jobs, or mass-updates during application-software upgrades) are tested on fully sized pre-production then just watch the rollback segments and the archivelog-volume (that archivelog destination doesn't run full).

And lastly, is it better to increase the datafile size or add another datafile to increase tablespace size.
The database in question is an OLTP Production database on Oracle 9205 & Solaris 5.8

Regarding file size: As most of my databases medium OLTP-databases (300 GB to 2 TB) I limit filesize to 4 GB, in history rather 2 GB.

*) Running backup in 5 streams and 7 files per backup-set requires at least 35 equal big datafiles. Having a few small files for the small and tiny tablespaces and all the usage in 10 x 30 GB the 5 backup streams will work only a few minutes, and the last one will run very long...
==> What's your current volume and backup-duration ?

*) when copying the data files for creating your fully-sized pre-production system the same rules apply.
*) The situations when filesizes greater than 2 GB or 4 GB cause problems are decreasing year by year, but e.g. tar will for longer time be limited to 8 GB files. In history I remember a copy-tool for secure file transfer stopped ad 2 GB and a compression tool too.
*) If you really want to use excessive file sizes, then make sure that all tools used and the backup system support such huge files.