Is it better to distribute a large database over several dbf files than over a single one?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
The number of files is irrelevant from a performance perspective. The number of spindles those files are distributed over is, on the other hand, critically important to performance. If you are using a reasonably modern SAN and the additional files would be created on the same mountpoint, there will be no meaningful performance difference since the data is being spread over the same number of physical disks. If additional data files would be placed on different physical disks, there would likely be performance benefits to that.
There may be management benefits to having multiple smaller, consistently sized data files if that makes it easier to move files from one physical location to another. If you have a bunch of 5 GB data files, for example, and you generally allocate 30 GB mountpoints on the SAN, it may be easier to have up to 6 data files per mountpoint so that if you need to move data files from one mountpoint to another, you can do so relatively easily. If you've got 4 mountpoints, each with 4 data files, you can move all the data files off of one mountpoint and distribute them over the other 3 mountpoints if that ever became necessary (for example, if you started having performance problems on one mountpoint). If you had one data file per mountpoint each of which was 20 GB, you couldn't easily move those data files around. Additionally, if a single data file became corrupt, it would generally be quicker to restore a 5 GB file from backup rather than a 20 GB file.
Of course, the number of data files needs to be reasonable. If you've got a 20 TB database, 5 GB data files would mean that you needed to manage 4000 separate files which would probably be excessive and would probably cause checkpoints to start to take longer than you'd like.