DB2 V8 Experiences – Access path patterns

Date: 
Tuesday, 13 lipanj, 2006 - 16:00 - 16:45
Speaker: 
Dirk Johann

Full Presentation Abstract:
The DB2 V8 Optimizer has many enhancements under the hood. As DBAs it is our nature to care about performance implications during migration. At several customers, we implemented procedures that kept us in control of access paths and insured good SQL performance before V8 migration. In this presentation, I will show the methodology we used to implement a V8 early precheck of all dynamic and static SQL while still on a production V7 system. Not only were we able to predict and evaluate the resulting V8 access paths, we also determined that patterns of expected access path changes will occur using the V8 Optimizer. By making such Optimizer decisions transparent, we took our performance-securing measures one step further. By using the results of the early precheck in combination with the new function of the V8 RUNSTATS utility regarding the column distribution statistics and skewed columns, we were able to influence Optimizer decisions under V8.

Presentation Outline:
1.Comparing access path changes between two DB2 Versions: the procedure
Simulating a V8 comparison environment
Evaluating V7 and V8 differences
2.A look at specific types of access path changes that are typical under V8
Static SQL
Dynamic SQL
3.Risk control management
Pinpointing which access path changes are relevant
Securing access paths under V8
4.Exploiting V8 Column Distribution Stats
Nourishing the V8 Optimizer with extended statistics
And, how early recognition of expected access path changes can help
5.Summary
Presentation Bullet Points:
Stage a V8 environment in which to do an SQL performance early precheck
Identify the Optimizer differences between V7 and V8
Show expected access path changes when migrating
Show access path change patterns for static and dynamic SQL
Opportunities to secure application performance using extended features of the V8 RUNSTATS

 

sqladria

SQL Adria is the independent, non-profit organization that gathers relational database users for Croatia and Slovenia. It was founded 1994. and in the same year it has become the regional user group.

Follow us

facebook
linkid
twitter

 

Events in 2023

VODICE -  12th - 15th June 2023