By default, each histogram has 20 steps, which provides good performance and modeling for columns that have an even distribution of values. A higher number of steps can increase the accuracy of I/O estimates for columns that:
Have a large number of highly duplicated values
Have unequal or skewed distribution of values
Are queried using leading wildcards in like queries
The histogram tuning factor default of 20 automatically chooses a step value between the current requested step value (default 20) and the increased steps due to the factor (20 * 20 = 400) so that Adaptive Server automatically chooses the optimal steps value to compensate for the above cases. Overriding the step values should take into account the larger number of steps already introduced by the histogram tuning factor.
If your database was updated from a pre-11.9
version of Adaptive Server, the number of steps defaults to the
number of steps that were used on the distribution page.
Increasing the number of steps beyond what is needed for good query optimization can degrade Adaptive Server performance, largely due to the amount of space that is required to store and use the statistics. Increasing the number of steps:
Increases the disk storage space required for sysstatistics
Increases the cache space needed to read statistics during query optimization
Requires more I/O, if the number of steps is very large
During query optimization, histograms use space borrowed from the procedure cache. This space is released as soon as the query is optimized.
