Three basic steps of SQL tuning.

Probably you have heard many times about performance tuning of Oracle. And one of the major part of performance tuning is to tune the SQL statement so that it generates better execution plan and performs well. It will be easy to tune the SQL statements if we remember three basic steps of SQL tuning. The three basic steps of SQL tuning is listed here.

Step1: At first we need to identified the high load SQLs or top SQLs that are responsible for performance problem or in another word the sql that consume application workload and system resources. We can easily identify them by reviewing past SQL execution history available in the system.

Step 2: Now generate execution plan of those high load sql statements and verify that the execution plans produced by the query optimizer for these statements perform reasonably.

Step 3: Implement corrective actions to generate better execution plans for poorly performing SQL statements. While taking corrective actions there is many considerations. Hopefully I will discuss these consideration in my blogs one by another.