A Methodical approach of Oracle Performance Tuning
Moving Beyond a Symptomatic Approach -This article is from the book - Oracle Performance Survival Guide: A Systematic Approach to Database Optimization.
The shift from ratio-based to wait-based tuning has resulted in radical improvements in our ability to diagnose and tune Oracle-based applications. However, as noted previously, simplistically focusing on the largest component of response time can have several undesirable consequences:
To avoid the pitfalls of a narrow wait-based analysis, we need our tuning activities to follow a number of well-defined stages. These stages are dictated by the reality of how applications, databases, and operating systems interact. At a high level, database processing occurs in layers, as follows:
1. Tuning Application layer ( Application code issues SQL (and PL/SQL), Requests to the database)
Activity in each of these layers influences the demand placed on the subsequent layer. For instance, if an SQL statement is submitted that somehow fails to exploit an index, it will require an excessive number of logical reads, which in turn will increase contention and eventually involve a lot of physical IO. It's tempting when you see a lot of IO or contention to deal with the symptom directly by tuning the disk layout. However, if you sequence your tuning efforts so as to work through the layers in order, you have a much better chance of fixing root causes and relieving performance at lower layers.
Here's the tuning by layers approach in a nutshell:
Problems in one database layer can be caused or cured by configuration in the higher layer. The logical steps in Oracle tuning are therefore