I have a server with three directly attached disk controllers. Each of these can provide internal redundancy and IO-striping accross disks, but I'm wondering what is the best way to configure the whole system.
Would it be sensible to allocate three datafiles for each tablespace, each datafile being created in a mount provided by a single controller? Oracle seems to distribute data somewhat evenly accross datafiles when doing inserts, but would this be enough striping for queries?
We are mostly concerned about read performance for single aggregate queries (data warehouse) and want to read data in parallel from each controller.
One answer would be to use ASM or LVM to do the striping, Oracle would then see only one mount point where to store datafiles. We do have a configuration with ASM + number of JBOD-disks; it does perform reasonably well, but ASM has it's own issues and we are considering alternatives.
Best Answer
Your plan of adding three datafiles on different mounts will not work well. Oracle doesn't stripe optimally within tablespaces. It just allocates extents in a reasonably efficient manner. The method you suggested is striped better than one disk, but it's not even close to optimal. Basically, you can't have really good striping without ASM or an external disk management system.
The other problem is that having three independently redundant arrays is either going to waste a lot of disk space or create three seperate points of failure. Think about it - if you are keeping one copy of each data block in each controller, then within that controller you will have multiple copies of the data again. You'll end up with 4, 6 or 9 copies of each block. That's a massive waste of resources. Conversely, without redundancy across controllers you are turning each controller into an SPOF (Single Point Of Failure).
Essentially you have two options:
Let Oracle manage the RAID functionality entirely. Turn off redundancy in the three controllers, set up ASM with normal/high redundancy. Create your diskgroups using an equal number of disks from each controller. Create three failure groups, one for each controller. This will achieve the redundancy and striping that you want.
Manage the redundancy entirely without Oracle. Use LVM or similar system to present a completely redundant, appropriately striped disk to Oracle. This will probably also involve turning off redundancy within each controller, and letting LVM manage the redundancy across controllers. The result is the same.
TL:DR - Your redundancy across disks within each controller is pointless when you have multiple controllers. Disable it and create redundancy across controllers instead.