An APAR closed recently that corrects an issue that exist with how DB2 collects RUNSTATS HISTOGRAM statistics. This APAR is significant to achieving proper optimization within DB2 10 and DB2 9. It is an APAR that all, regardless of their use of the HISTOGRAM keyword today, should give serious consideration to tracking down and apply. I believe you will be glad you did.
To explain properly why this APAR is significant, Patrick Bossman, part of the DB2 for z/OS Query Optimization team at IBM Silicon Valley Lab,, offered to do a write-up on what this APAR is correcting and why. What follows is his guest bog post…..
Hopefully everyone is getting ready to have a nice weekend. Before you go, let me tell you about one of the most important APARs regarding query optimization.
If you are already collecting histograms, you want this APAR on. If you haven’t started collecting histogram statistics, I suggest applying this APAR before you start. This APAR introduces some changes to the way RUNSTATS will cut histogram quantiles. While investigating some query performance problems, the support team noticed some occasions where the histogram collection algorithm could produce a late bulge, particularly in the last non-null quantile (RUNSTATS puts NULLs into their own quantile). This late bulge in some cases rendered the histograms ineffective for their only purpose – accurately estimating selectivity, which could sometimes result in an inefficient access path being selected.
This APAR was taken to improve histogram quantile generation.
- Histogram collection will now have 3 dedicated quantiles. One for the lowest value, one for the highest non-null value, and one for NULL values (if the column is nullable). Essentially, we are putting the outlier values into their own quantiles. Quantiles where the outliers are included with the normal data can impair rather than improve selectivity estimation.
- The quantile cutting algorithm is improved to adjust the number of rows per quantile. This reduces the tendency to produce a bulge in the last non-null quantile, and it also results in producing more quantiles when there is skew on an early value.
- The algorithm to determine rows per quantile for index only has been improved which results in more better equi-depth histograms.
So this APAR improves the histogram quantile cutting algorithm in a variety of scenarios to provide a histogram that is more useful for estimating selectivity accurately. This APAR is important for the collection of more usable histogram statistics. So if you’re already collecting histograms, you’d want this on to improve the quality of the generated histograms. If you haven’t started collecting histograms, it’d be nice to have this on before you started.
Hope you have a nice weekend.
APAR PM27249 just recently closed on May 17, 2011. The PTFs for this APAR are UK67817 for DB2 9 and UK67816 for DB2 10. Currently there is no special handling associated with this APAR.
Pat has been a guest blogger in the past and his previous post have all been very well accepted. In case you might be interested in looking back, I have listed them here. His post on declared global temporary tables is especially good.