April 14, 2009

DB2 Optimizer Gets Back in Balance

To me, the thing that sets DB2 apart from other relational databases is its cost-based optimizer. The DB2 Optimizer uses a very sophisticated formula to calculate the access path with the shortest elapsed time. Elapsed time includes both the I/O time and CPU required to process the query based upon the chosen access path.

CPU cost estimations are based on the CPU MIPS ratio, which improves with each release of a new System z processor. I/O costing, however, isn't as precise and thus hasn't kept pace with the dramatic improvements in CPU speed.

When the optimizer estimates the cost of a query, it makes certain assumptions about whether an I/O will be required, which I/O type may be required (prefetch or random) and whether pages will be revisited due to bufferpool caching.

Regardless of the sophistication of the formula, the faster CPUs were beginning to expose a shift in the optimizer's balance between CPU and I/O in the overall elapsed time estimate. This caused some customers to see an access path burn more CPU in trying to anticipate I/O. (Due to the complexity in estimating CPU and I/O, the exact scenario that caused the high CPU burn situation cannot be specifically given.)

To help address this situation, IBM now gives customers the option to use an enhanced optimization formula through the new ZPARM OPTIOWGT system parameter. This new formula, initially made available through APAR PK61277, does a much better job of calculating the cost of I/O versus CPU. With this change the optimizer will more accurately choose a more efficient access path.

Initially, the default for OPTIOWGT as provided in APAR PK61277 was "DISABLE." However, after gaining experience with customers who've moved to new System z processors and DB2 Version 9, IBM determined that the new formula should be enabled for all existing customers. So with the recent release of APAR PK75643, the OPTIOWGT default is now "ENABLE."

To date IBM hasn't had any customers report a problem with enabling the new formula. However, if OPTIOWGT is enabled and is negatively impacting your performance, contact IBM so they can advise you on the best way to move forward. The problem may or may not be related to OPTIOWGT.

Of course, the most powerful weapon in minimizing the impact of access path regressions across a static rebind is the DB2 9 access path stability feature (PLANMGMT). Customers are strongly encouraged to exploit this feature. The introduction of the Optimization Service Center has also simplified access path analysis.