Oracle - large or small data files sizing? Oracle Database Tips by Donald Burleson

Question:  I just moved to a 64-bit server and I want to know if I should make large Oracle data files (greater then 2 gigabytes).  Also, is this a good policy of having 4 - 1GB datafiles OR its good to have 1 datafile of 4GB?

Answer:  Like the answer to all Oracle questions, it depends. If you have four disks, and this data file has a very read/write rate, then striping the files across the 4 disks can relieve I/O contention.

Creating large data files on a single disk spindle can cause high I/O latency, especially when:

• The table spans multiple cylinders (requiring read-write head movement).
   
• Multiple hot spots exist on different cylinders on the disk for the same file.

If you don't have multiple disks it's a moot issue, and I really detest some of the 144 gig disks because they shake like an out-of-balance washing machine when I/O rates get high and the read-write heads scream back-and-forth attempting to service all the data requests. 

For more on Oracle file sizing and disk I/O tuning, see these books:

- Oracle Tuning: The Definitive Reference

- Oracle Disk I/O Tuning

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of my favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.