Oracle to DB2 UDB Conversion Guide2003

2.4 Performance tuning

Performance tuning is the process of increasing the effectiveness of your queries and decreasing the amount of time that they take to execute. However, the actual process of tuning the performance is not simply limited to modifying queries until they work better and faster. Rather, the process includes hardware configurations, operating system adjustments, application code optimization, interface considerations, database parameter changes, and finally query optimization.

While it is beyond the scope of this book to offer a full treatment on performance tuning, you will find here a list of links and resources that will allow you to learn about tuning the performance of your DB2 UDB-based application:

http://www7b.software.ibm.com/dmdd/zones/porting/tuning.html

Methodology

Here is a set process that works well when conducting any type of performance tuning:

• Define the objectives: What is wrong or what do you want to accomplish?

• Determine information to be analyzed

• Determine the monitor(s) to be used

• Test and obtain monitor data

• Analyze the information

• Determine the changes required

• Implement changes (one at a time) and go back to step four

By following this process, and carefully recording your settings, adjustments, and results, you should be able to achieve incremental improvements in performance, or determine that you have reached an optimal set of parameters, and move on to the next tuning task.

Training and tutorials

One of the best ways to learn about DB2 UDB performance tuning is to take the IBM course entitled "DB2 Universal Database Performance Tuning and Monitoring Workshop." This course fits in well with the "DB2 UDB system administration courses" that are recommended for the DBAs at your site.

There are also a number of tutorials that address specific performance issues.

Documentation

There is also a rich source of information on performance tuning in the DB2 UDB documentation. Look at the DB2 UDB Administration Guide: Performance manual on-line or under the document name of db2d3e80.pdf on the product CD. 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.

Information sources

The IBM DB2 Developer Domain contains a number of helpful articles that will help you increase the effectiveness of your use of DB2 UDB. Enter the key words performance tuning into the search box at the top of the page:

http://www7b.software.ibm.com/dmdd/zones/porting/index.html

The following articles will be provided:

• SQL Procedures Performance: Hints and Tips

• SQL Access to DB2 Monitoring Data: Capturing Snapshots

• Tuning DB2 Universal Database Using the Statement Event Monitor

• When We Think That the Optimizer Doesn't Get It Right

• Tuning DB2 SQL Access Paths

As you address more specific issues in performance tuning (or in other aspects of porting), be sure to keep this search mechanism in mind.

Useful tools

Part of performance tuning is being able to monitor and measure the items to be analyzed. Here are some tools that will be helpful:

• IBM Performance Expert: This IBM tool integrates performance monitoring, reporting, buffer pool analysis, and a Performance Warehouse function into one tool.

• Configuration Advisor: One of the DB2 wizards that provides configuration recommendation based on the workload provided. This tool can be accessed through DB2 Control Center.

There are also a number of other tools that will be of value as you administer your DB2 UDB system. Also review the DB2 UDB documentation for information on the EXPLAIN facilities, the Snapshot Monitor, the Event Monitor, and the Health Monitor. These tools also help determine problem areas and the solutions that can eliminate them.