When faced with an obviously IO-bound database, it's tempting to deal with the most obvious symptom—the IO subsystem—immediately. Unfortunately, this usually results in treating the symptom rather than the cause, is often expensive, and is often ultimately futile. Because problems in one database layer can be caused or cured by configuration in the higher layer, the most efficient and effective way to optimize an Oracle database is to tune higher layers before tuning lower layers:
- Reduce application demand to its logical minimum by tuning SQL, optimizing physical design (partitioning, indexing), and tuning PL/SQL.
- Maximize concurrency by minimizing contention for locks, latches, buffers, and other resources in the Oracle code layer.
- Having normalized logical IO demand by the preceding steps, minimize the resulting physical IO by optimizing Oracle memory.
- Now that the physical IO demand is realistic, configure the IO subsystem to meet that demand by providing adequate bandwidth and evenly distributing the resulting load.