I have recently encountered an issue where after running a COLLECT STATS statement on a table, the statistics displayed in the HELP STATS results aren't updating. I found that if I DROP STATS on the index or column and then re-collect stats, it will correctly display.
Can anyone tell me the reason for this, and what the solution may be?
are you on TD14.10?
Then it might be related to the new stats features, the optimizer might have skipped the collection.
See Carrie Ballinger's Statistics Threshold Functionality 101
You can do an EXPLAIN COLLECT STATISTICS statement on the stats that did not change when you thought they should, and the explain text will indicate whether or not stats were collected, or skipped. Here is an example of how that would look. The text will even tell you if the skipping was due to a user-specific threshold setting or the system default threshold setting.
We SKIP collecting STATISTICS for ('o_orderdate, o_orderkey'), because the age of the statistics (5 days) does not exceed the user-specified time threshold of 10 days and the estimated data change of 5% does not exceed the system-determined change threshold of 20%.
Dieter and Carrie,
Can statistics be forcibly collected? If I wanted to ignore the system threshold, which is very low in our site at 1%, how would I do that?
If you are worried about too much skipping leading to stale histograms and bad plans, it is easy to force recollections. Just add a USING THRESHOLD clause on your COLLECT STATISTICS statement with either a number of days or a threshold of change that you want, or add USING NO THRESHOLD, which means no skipping will ever take place for that statistic.
However, if you are using the default system threshold option that is on by default in all 14.10 and above systems, a different, customized threshold for each statistic is determined by the optimizer, based on characteristics and patterns in the data. It will not be the same threshold of change for each statistic, and its usually more in the 6% to 8% range or more, not 1%.
I would guess that a very low system threshold, like 1%, was set by using the DBA-defined DefaultUserChangeThreshold option, which will override the system default SysChangeThresholdOption. So that system-level setting could be disabled if is on, or changed it to something higher.
That said, a low system change threshold of 1% should end up "inhibiting" the incidences of stats skipping, not "increasing" them. With a 1% system-wide change threshold, stats will be fully collected when they are requested if the table has changed by 1% or more, and stats will only be skipped if the table has not changed, or changed less than 1%. The way thresholds work, skipping happens only if the threshold has not been met. 1% is the lowest threshold you can define, so depending how volatile this table is, stats recollection skipping may not happen very often.
I didn't refresh my screen before posting my last comment. Thanks so much for further explanation, Carrie!
DefaultUserChangeThreshold is not enabled (value in DBS Control is 0). The output from the HELP COLLECT STATISTICS stated:
1) First, we lock pIDWDetailData.ACCT for access.
2) Next, We SKIP collecting STATISTICS for ('ACCT_ID '), because the
estimated data change of 0.80 % does not exceed the
system-determined change threshold of 1 %.