HELP - Setting up Disk I/O subsystem for a large database

Hi,

Let me apologize ahead of time for the lack of precision in what I'm going to ask, I come from a development background and have recently been introduced to what (for my experience) is a large database. The following is longwinded, but I want to provide as much explanation as I can get the more specific answers.

The database in question three logical layers.

First is the equivalent of several 'star' schemas, each for one of the driving entities that the database holds. By 'star's schema, I mean that there is a table that hold one of the driving entities and several detail tables that are related to the driving entity table via foreign keys. This part of the overall schema is accessible via a web application for maintenance purposes, so overall this part of the schema must be indexed for on-line access. One of the driving entities represents customer accounts, of which several million are possible. Another driving entity represents securities trades and contains up to 4 years history at about 9 million per year.

The second level is a series of materialized views, which are drawn from the tables in the on-line schema to join related driving entities and speed the summarization of the data contained in the on-line database. The part of the overall schema is accessible only through reports, so it is indexed more like a data warehouse.

The third level is a set of regular views that are setup basically one view per report to make report writing easier.

The issue is the size of the database when fully implemented. We estimate that a development instance of this database will weigh in at about 40GB
(much less history stored) and a production instance at about 250GB. These
sizes are vastly larger than what was originally planned, so our server is experiencing difficulty and we're looking at how to re-configure it to improve performance. We are starting to do things like partitioning indexes, tuning back redo logging and strategically placing database data files, but don't think these alone will do the trick.

As the server is currently I/O bound (only 4 SCSI controllers for 250GB of disk on ~20 disks), we are focusing on the disk I/O system first. We've found the Oracle documentation on designing and tuning for performance and have not really found what we need. Don't get me wrong, there is a huge amount of data and many courses of action contained in the doc, but little in the way of rules of thumb. As an example, I seem to remember that it used to be recommended that table and index data be on separate I/O paths
(different disks or better yet, different disks on different controllers).
We will eventually get to the level of application knowledge required to use the Oracle tuning documentation only if we can get the app to perform well enough to get management to continue it's development.

All that said, are their any rules of thumb that I can apply to setting up the server to maximize database performance?

Thanks,

    Jim Harrington Received on Thu Nov 29 2001 - 17:06:14 CST