Bookmark and Share

Recent Posts

Optimization Statistics Feedback in DB2 11

October 15, 2013

Due to the CPU costs involved, DBAs are typically very selective when it comes to collecting column-level statistics. In fact, most DBAs gather only standard or default statistics using the RUNSTATS, TABLE(ALL), INDEX(ALL), KEYCARD parameters. This is unfortunate, because when column-level statistics are collected, the optimizer has the data it needs to choose a more efficient access path.

However, with the soon to be released DB2 11, IBM is addressing this issue by externalizing information that's gathered from BIND, REBIND or PREPARE statements. During the access path calculation process, the optimizer will identify missing or conflicting statistics. This information will be written to SYSIBM.SYSSTATFEEDBACK. DB2 will also provide feedback when the EXPLAIN statement is used. This information will be written to the DSN_STAT_FEEDBACK table.

In both cases, the feedback information can be used -- either by a DBA or through tooling -- to generate RUNSTATS syntax that will collect stats needed to help the optimizer choose an efficient access path.

*This post was edited for clarity on October 21, 2013

Posted October 15, 2013| Permalink

comments powered by Disqus