We have two production databases (of sizes 300GB and 200GB respectively) in Oracle 8i. We are planning to upgrade these into Oracle 10g. Now we have got SAN storage with 42 disks (146G for each disk). How do we use this storage for getting the optimal performance? The storage is Netapps and the volume manager is Veritas on Sun 5.9. Is there any advantage to using separate LUNs for each database?
The optimal performance can only be designed once you know your data access patterns. When you know which tables and indexes are accessed most frequently, you can separate them onto different disk devices so as to not create hot spots for your I/O. You can query V$SEGMENT_STATISTICS (which is not available until Oracle 9i) to see the segments (tables or indexes) with the most physical reads and writes. Prior to 9i, you can set up Statspack and wade through the information there on a file-by-file basis.
But I can give you some overall guidelines. In a SAN, I try to separate my database's files from other database...
files. This way, an overactive database, from an I/O perspective, does not negatively impact another database. This can also be a good idea from a storage management perspective. If one database grows rapidly, its growth will not constrain growth of the other database.