Question:
Why statistics is not updated after executing the collect statistics command. The statistics “collection Date” not showing the last executed date while the statistics collect command was successful.
select date;
Current Date
------------
2019-08-02
collect stats column Calendar_Dt_End_Dttm on DBStore.CALENDAR_DATE;
*** Update completed. One row changed.
*** Total elapsed time was 1 second.
SELECT DatabaseName
,TableName
,ColumnName
,CAST(LastCollectTimeStamp AS Date) As CollectionDate
,CAST(LastAlterTimeStamp AS Date) As LastSubmitDate
,StatsSkipCount
FROM DBC.TableStatsV
WHERE ColumnName IS NOT NULL
and databasename= 'DBStore'
and tablename='CALENDAR_DATE'
and columnname='Calendar_Dt_End_Dttm';
Answer:
You (Usually DBA) can check the parameter SysChangeThresholdOption in DBS Control.
DBS Control Record - Optimizer Statistics Fields:
1. DefaultTimeThreshold = 0 (Disabled; Default value)
2. DefaultUserChangeThreshold = 0 (Disabled; Default value)
3. SysChangeThresholdOption = 0 (Enabled with DBQL ObjectUsage; Default value)
4. SysSampleOption = 0 (Enabled; Default value)
5. BLCStats = 0 (Enabled; Default value)
The SysChangeThresholdOption is set to "Enabled with DBQL OUC" by default.
This means that the system calculates for every statistic a threshold to decide if the statistics needs to be recollected on the next "collect statistics" operation.
The system-determined change threshold is calculated by historical statistic records and Object Use Count (OUC) rows. We can see the value of the current threshold by running an explain command of the collect stats statement for the statistics database are interested in.
explain collect stats column Calendar_Dt_End_Dttm on DBStore.CALENDAR_DATE;
Explanation
-----------------------------------------------------------------------
1) First, we lock DBStore.CALENDAR_DATE in TD_MAP1 for access.
2) Next, We SKIP collecting STATISTICS for ('Calendar_Dt_End_Dttm '),
because the age of the statistics (258 days) does not exceed the
system-determined time threshold of 9999 days and the estimated
data change of 200 % does not exceed the system-determined change
threshold of 9,999.98 %.
3) We do a single-AMP UPDATE step from DBC.StatsTbl by way of the
primary index "{LeftTable}.Field_2 = '3400ABD10000'XB" with a
residual condition of ("DBC.StatsTbl.StatsId = 11").
4) We spoil the statistics cache for the table, view or query.
5) We spoil the parser's dictionary cache for the table.
-> No rows are returned to the user as the result of statement 1.
If you want the stats of this column not to be skipped, manually collect stats on the given column exclusively with no threshold.
COLLECT STATISTICS USING NO THRESHOLD COLUMN (columnname) ON databasename.tablename;
To control sample percent, specify using new syntax.
COLLECT STATISTICS USING SAMPLE 2 PERCENT COLUMN (columnname) ON databasename.tablename;
Please note that, once statistics is collected, future re-fresh at table level will honor this setting.