There are myriad approaches for tuning Oracle databases. If one is not aware of the specific applicability of each of them, it can be mind-numbing to select those with the maximum potential for eliminating your system's bottlenecks.
Based on our years of experience, we use a combination of three strategies based on Oracle AWR reports :
We primarily look at ratios in the instance Efficiency section of AWR Reports. Sample ratios we look at are:
We analyze wait events listed in the Top Five Timed Events and Wait Events sections of AWR. Some of the wait statistics we look at are:
We also look at some of the common wait events in an AWR Wait Events section, such as:
Workload Analysis comprises Workload analysis an investigation of two critical areas of a database's performance:
Analyzing statistics in the Load Profile section of the AWR provides insights to the load and type of activity being performed. Examples of indicators of problems are:
We use a combination of ratio, wait and workload analysis to obtain consistent, excellent results when commissioned to tune Oracle databases.