Neoview Control Query Default (CQD) Reference Guide (R2.5)

4 Managing Automated STATS Generation
This section describes these CQDs that are used for managing automated STATS generation:
• “USTAT_AUTO_CV_SAMPLE_SLOPE” (page 17)
• “USTAT_AUTO_PRIORITY” (page 17)
• “USTAT_MAX_READ_AGE_IN_MIN” (page 18)
• “USTAT_MIN_ROWCOUNT_FOR_SAMPLE” (page 19)
• “USTAT_NECESSARY_SAMPLE_MAX” (page 19)
• “USTAT_OBSOLETE_PERCENT_ROWCOUNT” (page 20)
USTAT_AUTO_CV_SAMPLE_SLOPE
Update Statistics and ReorgCategory
For update statistics automation, or when using the NECESSARY keyword, this CQD is
used to change the sample size for subsequent update statistics, for cases where prior
update statistics runs have determined that the dataset for a histogram contains skew. This
CQD is a multiplier of the column CV in the HISTOGRAMS table, which was populated
during the last update statistics. CV is a measure of skew.
For the NECESSARY keyword and automation, update statistics always samples. The
sample percent is determined using this equation:
MINOF(HIST_DEFAULT_SAMPLE_RATIO + CV * USTAT_AUTO_CV_SAMPLE_SLOPE,
10%), limited to USTAT_NECESSARY_SAMPLE_MAX rows.
Description
0 through max float
value
A column with skew does not cause an increase in the sample
size.
‘0’
The default value is 0.5.
Values
The default value should be used in most cases. However, a large skew in a table can result
in a large sample size when using the NECESSARY keyword (for example, when using
update statistics automation). A larger sample size results in update statistics taking longer
to run. Lowering the value of this CQD could help reduce the sample size and the time it
takes to run update statistics.
Usage
Not applicableProduction usage
Specifying a smaller value reduces the sample size and the time it takes to run update
statistics. However, it may reduce the accuracy of histograms when a table has skew in the
data. By specifying a larger value, the opposite occurs.
Impact
ServiceLevel
Use this CQD with USTAT_NECESSARY_SAMPLE_MAX.Conflicts/Synergies
Not applicableAddressing the real
problem
Neoview Release 2.4 SP1Introduced in release
Not applicableDeprecated in release
USTAT_AUTO_PRIORITY
Update Statistics and ReorgCategory
Sets the priority of the compiler process while running update statistics during the update
statistics automation maintenance window.
Description
USTAT_AUTO_CV_SAMPLE_SLOPE 17