PH22023: QUERY HAS ZERO COST, BYPASSING WLM SETTINGS

APAR status

• Closed as fixed if next.

Error description

• Accurate statistics are key to good performance.  When no
  statistics are available for a column, the Optimizer will use
  default filter factors when evaluating predicates referencing
  that column, which in many cases is not good enough; the use of
  default filter factors can result in an overestimation of column
  cardinalities, which can in turn result in an overestimation of
  a query's cost.
  .
  If the registry variable DB2_EXTENDED_OPTIMIZATION is set to
  "BI_INFER_CC", we fabricate column cardinalities for regular
  (inner) join columns, which improves our filter factor and
  cardinality estimates.  Currently, this functionality does not
  exist for outer join columns.  As a result, a complex query with
  many outer joins can produce an access plan using default filter
  factors for the join predicates, causing the cardinality and
  cost estimates to increase until they overflow.  Once the cost
  overflows, it becomes zero.
  .
  The zero cost means this query will bypass any WLM (workload
  management) thresholds designed to throttle complex or costly
  queries.
  .
  This fix will extend the fabrication of column cardinality
  estimates on columns without statistics to support outer join
  columns.  By fabricating column stats, we avoid default filter
  factors, so the cardinality estimates will be much more
  reasonable, resulting in a more realistic cost estimate for the
  query plan.
  

Local fix

• Run ANALYZE to collect statistics on all the columns referenced
  by join predicates.  If there are multiple join predicates
  between tables (e.g. "T1.c1=T2.c1 and T1.c2=T2.c2"), then
  collecting column group statistics may help with cardinality
  estimates. Column group stats might also help if we have
  multiple local equality preds on the same table.
  

Problem summary

• Please see the problem description.
  

Problem conclusion

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

Applicable component levels