Performance Monitoring, Analysis, and Tuning

Second only to backup and recovery, performance tuning is one of the most valuable functions that an ORACLE database administrator can perform. If a DBA can tune an instance such that work can be completed more quickly, then that increase in performance can be quantified in terms of tangible dollars saved in unnecessary hardware expenditures and, more importantly, tangible dollars gained in increased business competitiveness from characteristics such as improved customer satisfaction, improved work process efficiency, and better business decisions.

Performance tuning is an iterative process; it is never "finished". When one hardware resource bottleneck is alleviated, a different hardware resource becomes the "new" bottleneck. When the most expensive query in an instance has been tuned to perfection, a different SQL statement will become the "new" most expensive query. Further, data distributions change as data accumulates, patterns of end-user work change over time, etc. Personnel performing tuning (particularly SQL tuning) are encouraged to recall Pareto's Rule: 20% of the work will consume 80% of the resources. Tuning the top 10-20% may require 5-20 hours per week of DBA time. Attempting to tune 100% of the processes exponentially increases the time requirements to 40-160 or more hours per week. Adopting the perspective that the most expensive performance concerns will be continuously tuned over time in an iterative manner until performance is at an acceptable level will at least allow milestones to be established for this otherwise endless task.

This site will not attempt to re-iterate most of the general tuning information that can be found in the readily-available ORACLE documentation and third-party tuning guides, except where the information is exceptionally note-worthy and quantifiable. The focus of this section is to address information specific to tuning ORACLE on NT that is not readily-available from other sources of information.