Applying statistics recommendations to statistics profiles automatically
You can automatically apply statistics recommendations that are generated during the query optimization process to statistics profiles.
Before you begin
Procedure
To apply statistics recommendations from the query optimization process to statistics profiles:
Set the value of the STATFDBK_PROFILE subsystem parameter to YES.
Results
Whenever Db2 writes new statistics recommendations to the SYSIBM.SYSSTATFEEDBACK catalog table, it also updates existing statistics profiles based on the recommendations:
- If no profile exists, Db2 creates new profiles for some recommendations. The new profiles collect existing statistics (similar to the result of
RUNSTATS SET PROFILE FROM EXISTING STATS) plus statistics for new and existing uncollected statistics recommendations. - Existing profiles are modified depending on the type of statistics recommendation:
- TYPE='T'
- No profiles are changed, and no profiles are created for this recommendation type.
- TYPE='I'
- If the existing statistics profile does not include the index, the index is added with default collection. Any existing list of indexes without FREQVAL or HISTOGRAM specified is changed to INDEX(ALL).
No profiles are created for this recommendation type.
- TYPE='C' with NUMCOLS = 1
- If the existing profile does not include the column, the column is added.
No profiles are created for this recommendation type.
- TYPE='C' with NUMCOLS > 1
- If the existing statistics profile does not include the column group, the column group is added with COLGROUP syntax. KEYCARD recommendations add the corresponding index.
If no statistics profile exists for the table, a new profile that includes the column group and INDEX(ALL) is created.
- TYPE='F' or 'H'
- If the existing statistics profile does not include the frequency or histogram statistic, it is added. Db2 automatically determines the number columns to collect. Histogram statistics are added without a NUMQUANTILES specification. The frequency or histogram statistics are added in COLGROUP syntax. However, if the recommendation is for an index on expression, INDEX syntax is used instead.
In no statistics profile exists for the table, a new profile that collects the frequency or histogram statistics and INDEX(ALL) is created.
- If Db2 detects certain statistics that provide no value for query optimization, such as certain multi-column frequency statistics, it modifies the profile to prevent the future collection of the useless statistics. Db2 also removes the statistics from the relevant catalog tables and issues a message to indicate the removal.
- If CREATE INDEX statement is issued, Db2 adds the index to the existing statistics profile for the related table.
What to do next
