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

Display no warnings. Update Statistics Automation Server still
continues to record missing column statistics.
‘0’
Display only missing single column statistics warnings. These
include 6008 and 6011.
‘1’
Display all missing single and multi-column statistics warnings
for scans only.
‘2’
Display all missing single and multi-column statistics warnings
for scans and join operators only. Update Statistics Automation
Server continues to record missing column statistics.
‘3’
Display all missing single and multi-column statistics warnings.‘4’
The default value is ‘4’.
NOTE: Values ‘3’ and ‘4’ were introduced in R2.4.
Values
Update Statistics Automation Server captures these warnings and gathers appropriate
statistics. If you do not want to see these warnings, change the setting to “0”. If you want
to track the warnings, then you have a choice of which warnings you want to track. Each
setting gives you the ability to filter the warnings seen for missing single or multi-column
statistics for join or scan operations. This controls the resulting number of warning messages.
If poor query plans are being caused by cardinality estimations that seem to be off, you
can check the histogram statistics to see if statistics are being collected for those columns
and how accurate they are. If you don’t find statistics being collected, you could look for
the warnings by setting this CQD to the appropriate setting. Based on that you could take
appropriate action – either find out why USAS is not collecting appropriate statistics, or if
USAS is not being used ensure that update statistics is being run to generate those statistics.
Usage
Many tools divide a query into several steps. During the first phases volatile tables are
created and populated, the last phase usually joins all the volatile tables created in the
previous steps. Usually statistics are not needed for those volatile tables because the final
join is straight forward and the optimizer has no big choices. Nevertheless EMS is flooded
with useless warnings if you don’t set the warning level to 0. If possible, try to direct queries
from those tools to a dedicated service where you set the warning level to 0.
Production usage
Though the warnings give information about all statistics that are missing, it can be
overwhelming to get several warnings. Not all warnings may contribute to plan
improvements. The optimizer issues multi-column statistics warnings based on the search
path, some of which may not even impact the plan quality. Also, the cost of gathering
statistics on those columns may not bring commensurate benefit to a large number of
queries.
Impact
SystemLevel
Update Statistics Automation Server does capture these warnings and gathers statistics
felt appropriate for query performance. If you are using USAS then you may set the warning
level to low or 0.
Conflicts/Synergies
Not applicableAddressing the real
problem
Neoview Release 1.0; values ‘3’ and ‘4’ were introduced in Neoview Release 2.4.Introduced in release
Not applicableDeprecated in release
HIST_NO_STATS_REFRESH_INTERVAL
HistogramsCategory
Defines the time interval after which the fake histograms in the cache should be refreshed
unconditionally.
Description
HIST_NO_STATS_REFRESH_INTERVAL 33