Iterations of analysis and tuning

Analyzing the performance of buffer pools and tuning a DB2® system for optimum performance is a process that can serve several purposes and that involves one or more tasks dependent on your requirements and motivations.

Different tasks take varying amounts of time, might require different levels of knowledge, and require different level of information or support. This topic outlines some typical database administrator goals and tasks. It is basically about why, what, and when something should be done.

This figure is a graphical representation of observation, optimization, and simulation as an iterative process. — Figure 1. Observation, optimization, simulation – an iterative process

As described, buffer pool analysis and tuning is an iterative process that involves assessing the actual state, recognizing problems, identifying solutions in the form of changes, and applying changes to a system by means of Db2 ALTER BUFFERPOOL commands and SQL ALTER statements. The process is repeated by verifying the success of changes until further improvements are not visible or no longer economical. Buffer Pool Analyzer supports all the tasks described previously. The solutions it provides are described in the following topic.

Analysis and tuning can serve different purposes and can be performed at different levels. The previous figure outlines how you can combine several tasks for successful tuning over a longer period. You should have a work plan that clearly records your goals, the current state of buffer pool performance, the tasks you have performed, and the tuning actions you have applied. Consider also the following tips:

Review and analysis of the actual performance should always be the first step. It should also be repeated as the last step to verify the success of tuning.
Finding the optimal buffer pool usage and size can directly lead to a tuning action, or can be followed by an intermediate step to estimate the probable effects of changes.