Visual Explain with Statistics Advisor

Answer

The Statistics Advisor plug-in to Visual Explain V8 only works when Visual Explain V8 is connected to a DB2® for z/OS® V8 subsystem.

The optimizer depends on complete and accurate statistics to determine the cost of the available access paths. The optimizer uses the statistics to differentiate efficient from inefficient access paths. The lack of complete and accurate statistics is one of the most common causes of unstable and inefficient query performance. Visual Explain Version 8 contains a new capability: Statistics Advisor. In the Tune SQL section of Visual Explain, you can now click the Analyze button to invoke the Statistics Advisor. Statistics Advisor explains the statement and analyzes the tables, indexes, and columns that are referenced in the WHERE clause of the query. Statistics Advisor generates RUNSTATS utility statements to resolve incomplete, inconsistent, and stale statistics situations. In a few button clicks, you have RUNSTATS suggestions that are specific to your query and your environmental situation.

The following lists give some examples of the information that Statistics Advisor checks:

Incomplete statistics:
Statistics Advisor attempts to bring the statistics up to a base level. That base level means having column statistics on all columns that are used in WHERE clause, multi-column cardinalities to support joins, and available indexes, frequencies in situations where they are deemed likely to be useful.

Statistics Advisor checks for the following items:
- All tables and indexes have non-default statistics
- All columns which have a WHERE clause predicate should have column cardinality
- All indexes with 3 or more columns should have KEYCARD
- All multi-column joins between tables should have supporting multi-column cardinality

Inconsistent statistics:
Thresholds are available that allow minor inconsistencies to occur. Sometimes RUNSTATS is run inconsistently. For example, sometimes statistics are collected frequently on indexes, but not on table spaces. Thus, some statistics in customer situations have been inconsistent between indexes and table statistics, and indexed columns and non-indexed columns. This can mislead the optimizer to selecting inefficient access paths.

Statistics Advisor checks for the following items:
- Column cardinalities are less than or equal to table and index cardinalities that contain those columns
- The sum of frequencies is less than 1.0
- The number of frequencies is less than COLCARDF for column
- Unique indexes have FULLKEYCARDF equal to table CARDF
- Many more inconsistencies are checked.

Stale statistics:
Statistics Advisor checks if statistics are old. (The default setting for old is 180 days.) Statistics Advisor also checks if the filter factor for a predicate is unusually low given the circumstances. Sometimes when customers have collected frequencies for every column value, and later add additional column values, but do not refresh the statistics, the optimizer generates an extremely low filter factor. This low filter factor is a problem because the frequencies no longer reflect the values that exist. When a filter factor on a COL op LIT predicate seems extremely low, Statistics Advisor generates RUNSTATS to recollect the column cardinality and frequency on the column which has a predicate with an unusually low filter factor.

Statistics Advisor checks the following items:
- Is STATSTIME older than n days?
- Does the filter factor for COL op LIT predicates seem unreasonably low?

You can download Visual Explain, including the Statistics Advisor at http://www.ibm.com/software/data/db2/zos/osc/ve/details.html