Skip to main content

Search

Welcome to Tibero GTS!

ERROR_OPT_INVALID_HISTOGRAM_NDV(-28018) Error Occurs During Statistics Collection

Document Type | Troubleshooting

Category | Administration

Applicable Product Version | 7FS02PS

Document Number | TADTS031

 

Issue

An ERROR_OPT_INVALID_HISTOGRAM_NDV(-28018) error occurred during the statistics collection process.
This error occurred while collecting statistics on the STARTNODEID column of the AL_TRANSACTION_LOG_07, AL_TRANSACTION_LOG_08, and AL_TRANSACTION_LOG_10 tables, and the statistics collection for this column failed.
 

sys.log

[2025-04-26T00:33:17.340918] [FRM-1084] [I] THROW. ec=ERROR_OPT_INVALID_HISTOGRAM_NDV(-28018) [ Invalid ndv on column 'AL_TRANSACTION_LOG_07.STARTNODEID' frequency histogram (Current value: 125). ] (sql_id:(null), sub_sql_id:fngnts8r3az36, user:SYS, ap_module:(null), program:JDBC Thin Client, host:prdteaiap01) [stat.c:230:stats_load_internal]
[2025-04-26T00:33:17.340977] [OPT-1084] [I] Loading histogram of column AL_TRANSACTION_LOG_07.STARTNODEID failed. (obj_id=11431,col_no=14, ec=-28018)
[2025-04-26T00:33:17.342202] [FRM-1084] [I] THROW. ec=ERROR_OPT_INVALID_HISTOGRAM_NDV(-28018) [ Invalid ndv on column 'AL_TRANSACTION_LOG_08.STARTNODEID' frequency histogram (Current value: 125). ] (sql_id:(null), sub_sql_id:fngnts8r3az36, user:SYS, ap_module:(null), program:JDBC Thin Client, host:prdteaiap01) [stat.c:230:stats_load_internal]
[2025-04-26T00:33:17.342222] [OPT-1084] [I] Loading histogram of column AL_TRANSACTION_LOG_08.STARTNODEID failed. (obj_id=11433,col_no=14, ec=-28018)
[2025-04-26T00:33:17.347715] [FRM-1084] [I] THROW. ec=ERROR_OPT_INVALID_HISTOGRAM_NDV(-28018) [ Invalid ndv on column 'AL_TRANSACTION_LOG_10.STARTNODEID' frequency histogram (Current value: 143). ] (sql_id:(null), sub_sql_id:fngnts8r3az36, user:SYS, ap_module:(null), program:JDBC Thin Client, host:prdteaiap01) [stat.c:230:stats_load_internal]
[2025-04-26T00:33:17.347740] [OPT-1084] [I] Loading histogram of column AL_TRANSACTION_LOG_10.STARTNODEID failed. (obj_id=11437,col_no=14, ec=-28018)

 

 

Cause

Check of sys._dd_hist_head Table

SQL> select * from sys._dd_hist_head where obj_id in (select obj_id from sys._dd_col where obj_id
= (select obj_id from sys._dd_tbl where obj_id = (select obj_id from sys._dd_obj where name='AL_TRANSACTION_LOG_10' and subname is null)));
HIST_HEAD_ID     OBJ_ID     COL_NO BUCKET_CNT   NULL_CNT TIMESTAMP_NO                   SAMPLE_SIZE DISTINCT_CNT LOW_VAL                                            HIGH_VAL                                              DENSITY AVG_COL_SIZE  RESERVED1  RESERVED2  RESERVED3 RESERVED4      
------------ ---------- ---------- ---------- ---------- ------------------------------ ----------- ------------ -------------------------------------------------- -------------------------------------------------- ---------- ------------ ---------- ---------- ---------- ---------------

       5965      11437         14    2992160          0 2025/04/10 17:18:39                 149608          1000 I_TBIL_SVR1                                        I_TRAT_SVR1                                             .0001           11          7          0          0               0
For obj_id = 11437, col_no = 14 (STARTNODEID column), the statistics showed bucket_cnt = 2,992,160 and distinct_cnt = 1000.
In the internal logic, if bucket_cnt > distinct_cnt, the histogram is recognized as a Frequency histogram. However, by definition, distinct_cnt for a Frequency histogram must be 1, but it is currently 1000, causing the error.
 
It was confirmed that in the past, statistics were manually adjusted using DBMS_STATS.SET_COLUMN_STATS.
EXEC dbms_stats.set_column_stats(
  ownname => 'BSSDBA',
  tabname => 'AL_TRANSACTION_LOG_16',
  colname => 'STARTNODEID',
  distinct => 1000,
  density  => 0.001,
  force    => TRUE);
During this process, the statistics (especially NDV) were incorrectly adjusted, and later, when collecting statistics, there was also an issue with the logic that adjusts distinct_cnt after collecting the height histogram, causing the same problem to recur.
 
It appears that Tibero's internal NDV adjustment logic was insufficient; for example, it overwrote NDV without revalidating the relationship between the existing bucket_cnt and NDV, maintaining the structure where "bucket_cnt > NDV".
As a result, even though it was a height histogram, the internal logic interpreted it as a Frequency histogram, causing the error.

 

 

Solutions

Recollect statistics for the problematic columns to correct NDV and histogram information.
Alternatively, you can use the DBMS_STATS.DELETE_COLUMN_STATS function to delete the statistics for the column and revert to default statistics.
SQL> EXEC DBMS_STATS.DELETE_COLUMN_STATS
(ownname=> ' ' ,
tabname=> ' ',
colname=>' ');

To prevent cases where distinct_cnt is adjusted to be larger than bucket_cnt during height histogram collection, internal logic defensive code will be improved.

Note
Precautions When Using the DBMS_STATS.SET_COLUMN_STATS Function
  • When using Height histograms, the distinct_cnt value must not be set larger than bucket_cnt.
  • If arbitrarily adjusted, it may violate the criteria for Frequency/Height histograms, so consistency must be maintained.
     
Frequency Histogram (Frequency Distribution Histogram)
  • Each value occupies one bucket.
  • NDV (Number of Distinct Values) โ‰ค Number of Buckets
  • For Frequency histograms, the distinct_cnt value must be 1 to be correct.
 
Height-balanced Histogram (Height-balanced Histogram)
  • Records are distributed evenly across each bucket.
  • Used when NDV is greater than the number of buckets; if buckets exceed 256, Height-balanced histogram is forcibly applied.
     
Why is it recognized as Frequency when bucket_cnt > distinct_cnt?
  • Because Frequency histograms are collected with the structure "one value = one bucket," the definition assumes bucket_cnt must be greater than distinct_cnt.
  • Therefore, internally, if the condition bucket_cnt > distinct_cnt is met, the histogram is regarded as Frequency type.

 

 

 

 

 

 

Related to