The purpose of this document is to provide a generic method that will help you find and fix a performance problem in Oracle. Much has been written on the subject of Oracle Performance Tuning, and this document makes no attempts to supplant that material. Instead, this document will attempt to guide you along a process that will diagnose most common Oracle SQL performance problems.
There are 4 main areas of performance tuning:
In general terms, the impact of poor design follows the same order. Ie. SQL Tuning will deliver the most improvement, followed by Database Tuning, etc. This document deals only with SQL Tuning.
At times SQL Tuning will result in an optimal design, which still delivers sub-optimal performance. On such occasions the problem must be escalated usually to the DBA for resolution.
Every Oracle developer should have a copy of the Oracle Online Documentation a series of books in HTML format forming a complete reference of the RDBMS and other Oracle products. Critical to understanding performance tuning are the manuals:
If you dont have access to these manuals, see your DBA now. They are distributed on CD-ROM, but the number of times you will use them justifies the 550Mb on your hard disk for a local install. Alternatively, they are available online at http://tahiti.oracle.com