|
Step 12. Performance tuning
 |
Checklist for performance tuning
- Define the objectives
- Determine which information to analyze
- Determine how the monitoring data will be captured
- Test and obtain data
- Analyze the data
- Determine the changes required
- Implement changes (one at a time) and re-test
|
|
Performance tuning is the process of increasing resource consumption in some areas at the expense of others, with the goal of increasing overall system response time or throughput. Part of this process involves increasing the effectiveness and performance of your SQL statements. Another part involves determining the best settings of the various tuning "knobs". It also involves making necessary changes for hardware configuration, operating system configuration, application code optimization, interface considerations, and database parameter changes.
While it is beyond the scope of this Web site to offer a full treatment of the performance tuning process, a list of valuable resources is provided, which cover the process in more detail.
Methodology
Here is a process that works well when conducting any type of performance tuning exercise:
- Define the objectives - What is wrong or what do you want to accomplish?
- Determine which information to analyze
- Determine how the monitoring data will be captured
- Test and obtain the data
- Analyze the data
- Determine the changes required
- Implement changes (one at a time) and go back to step 4
Training and tutorials
One of the best ways to learn about DB2 performance tuning topics is to take the IBM course entitled DB2 9 for Linux, UNIX, and Windows Performance Tuning and Monitoring Workshop. This workshop explores performance issues affecting the design of the database and applications using the database, the major database performance parameters, and the different tools that assist in performance monitoring and tuning.
There is also a rich source of information about performance tuning in the DB2 documentation. In particular, refer to the Tuning Database Performance manual. This manual offers an extensive description of the tuning process (at various levels) and addresses many of the common areas where tuning can be most beneficial.
Other information sources
The DB2 Technical Library contains a number of helpful articles and tutorials that cover performance tuning topics. Seach the library using the key words performance tuning. As you encounter more specific issues in performance tuning, be sure to keep this Web site in mind.
There are many IBM Redbooks covering DB2 performance topics. Use the search mechanism to find the appropriate ones, or simply browse through the collection of available Redbooks.
Tools
A large part of performance tuning is being able to collect the right data, and to analyze and draw conclusions from the data. The IBM Performance Expert is a very helpful tool that integrates performance monitoring, reporting, buffer pool analysis, and a performance warehouse function all in a single place.
There are also a number of other for-fee tools that may be of value as you administer your DB2 system.
| 
