num_freqvalues - Number of frequent values retained configuration parameter
This parameter allows you to specify the number of most
frequent values
that will be collected when the WITH
DISTRIBUTION option is specified on the RUNSTATS command.
- Configuration type
- Database
- Parameter type
- Configurable Online
- Propagation class
- Immediate
- Default [range]
- 10 [0 - 32 767 ]
- Unit of measure
- Counter
Increasing the value of this parameter increases the amount of statistics heap (stat_heap_sz) used when collecting statistics.
The most frequent value
statistics
help the optimizer understand the distribution of data values within
a column. A higher value results in more information being available
to the query optimizer but requires additional catalog space. When 0 is
specified, no frequent-value statistics are retained, even if you
request that distribution statistics be collected.
You can also specify the number of frequent values retained as part of the RUNSTATS command at the table or the column level by using the NUM_FREQVALUES command parameter. If none is specified, the num_freqvalues configuration parameter value is used. Changing the number of frequent values retained through the RUNSTATS command is easier than making the change using the num_freqvalues database configuration parameter.
Updating this parameter can help the optimizer obtain better selectivity estimates for some predicates (=, <, >) over data that is non-uniformly distributed. More accurate selectivity calculations might result in the choice of more efficient access plans.
- Run the RUNSTATS command again to collect statistics with the changed number of frequent values
- Rebind any packages containing static SQL or XQuery statements.
When using RUNSTATS, you have the ability to limit the number of frequent values collected at both the table level and the column level. This allows you to optimize on space occupied in the catalogs by reducing the distribution statistics for columns where they could not be exploited and yet still using the information for critical columns.
Recommendation: In order to update this parameter you should determine the degree of non-uniformity in the most important columns (in the most important tables) that typically have selection predicates. This can be done using an SQL SELECT statement that provides an ordered ranking of the number of occurrences of each value in a column. You should not consider uniformly distributed, unique, long, or LOB columns. A reasonable practical value for this parameter lies in the range of 10 to 100.
Note that the process of collecting frequent value statistics requires significant CPU and memory (stat_heap_sz) resources.