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

7 Managing Histograms
This section describes these CQDs that are used to manage histograms:
• “CACHE_HISTOGRAMS_REFRESH_INTERVAL” (page 31)
• “HIST_AUTO_GENERATION_OF_SAMPLE” (page 31)
• “HIST_MISSING_STATS_WARNING_LEVEL” (page 32)
• “HIST_NO_STATS_REFRESH_INTERVAL” (page 33)
• “HIST_PREFETCH” (page 34)
• “HIST_ROWCOUNT_REQUIRING_STATS” (page 34)
• “HIST_USE_SAMPLE_FOR_CARDINALITY_ESTIMATION” (page 35)
CACHE_HISTOGRAMS_REFRESH_INTERVAL
HistogramsCategory
Defines the time interval after which timestamps for cached histograms are checked to be
refreshed.
Description
Unit is seconds.Unsigned integer
The default value is ‘3600’ (1 hour).
Values
Histogram statistics are cached so that the compiler can avoid access to the metadata tables,
thereby reducing compile times. The timestamp of the tables are checked against those of
the cached histograms at an interval specified by this CQD, in order to see if the cached
histograms need to be refreshed.
You can increase the interval to reduce the impact on compile times as long as you do not
need to obtain fresh statistics more frequently in order to improve query performance. It
may be that the default interval is too long and you would rather refresh the statistics more
frequently than the default one hour, in order to improve query performance at the cost
of increased compile times.
This setting depends on how frequently you are updating statistics on tables. There is no
point in refreshing statistics frequently when statistics are not being updated during that
time. On the other hand if you are updating statistics, or generating them for the first time
on freshly loaded tables frequently enough, and you want these to be picked up immediately
by the compiler because you have seen this to have a dramatic impact on plan quality, then
you can make the refresh more frequent.
Usage
Not applicableProduction usage
Longer histogram refresh intervals can improve compile times. However, the longer the
refresh interval the more obsolete the histograms. That could result in poor performance
for queries that could leverage recently updated statistics.
Impact
System or ServiceLevel
Frequency of update statistics run either using MAINTAIN or using Update Statistics
Automation Server.
Conflicts/Synergies
Not applicableAddressing the real
problem
Neoview Release 2.4Introduced in release
Not applicableDeprecated in release
HIST_AUTO_GENERATION_OF_SAMPLE
HistogramsCategory
Enables the Compile Time Statistics feature to automatically create a sample table when it
needs one, if one already does not exist.
Description
CACHE_HISTOGRAMS_REFRESH_INTERVAL 31