There is a camp in DBA-land (or was at one point, I haven't been through there recently) that held that optimal performance was to be found by micromanaging the storage infrastructure to such an extent that the systems and storage administrators had best get out of the way and just give them a pile of hard-drives, a server or two, and an occasional helping hand when asked.
These systems usually end up with a large number of RAID1 pairs with datafiles purposely and systematically spread across each and every one of them. Log drives would be used and similarly segregated. The ideas behind this method are pretty simple:
- By doing it this way, you segregate databases from other big IOP consumers so big users would never affect the rest of the system.
- By having lots of drives, the aggregate IOPS for the system is very high.
- By minimizing the storage abstraction layers you make the system overall more reliable, and importantly have lower latency.
There is a significant problem with this approach, and Chopper3 nailed it: maintainability.
A system like that is going to need constant and continual attention and fine tuning to keep performing at 'optimum' as databases grow/shrink, usecases change, applications and their usage patterns evolve, recovery from run-away conditions happen, and maintenance cycles change. The sort of architecture I describe above works best for write-almost-never/read-lots sorts of workloads.
It also is used when it really is important for the database to eeke every last fractional percentage point of performance out of the hardware. This is a budget decision in many cases, where it is deemed that DBA time is cheaper than more hardware. However, there are some HPC cases where it just isn't possible to make a bigger box so you have to optimize what you have. For the rest of us who aren't using monster database servers there are always upgrades.
The other camp, and the one I ascribe to, handles it differently. It puts more faith in storage abstraction, and tends to scale a lot better. Instead of umpty R10 pairs, you put all of those pairs into a RAID10 set; or alternately a small number of RAID10 sets. This allows IOP aggregation same as above, but each datafile/tablespace/database gets access to quite a bit of surge capacity. By using multiple R10 sets you can provide I/O separation for those critical databases and log-files that need it. The need to micromanage per-file peformance is greatly reduced.