3762843 - Query runs slowly for certain input values due to incorrect histogram selectivity - SAP ASE

• The same SQL statement shows different performance:
  - Fast execution for most input values
  - Slow execution for certain values 
  
  
• For values that generate a slow plan, the Optdiag histogram for the involved table shows:
  - Ranges with weight (selectivity) = 0 
  - However, data exists in those ranges when SELECT from table
  
  
• From Optdiag for the involved tables, the 'Sampling Percent' for columns show a different value from the ASE server configuration parameter "sampling percent". 
  i.e.
  1> sp_configure "sampling"
  2> go
   Parameter Name   Default     Memory Used   Config Value   Run Value    Unit    Type
   ---------------- ----------- ------------- -------------- ------------ ------- -------
   sampling percent           0           0              0              0 percent dynamic
  
  

  Histogram for column:                                                           "<COLUMN_NAME>"
  Column datatype:                                                                char(20)
  Requested step count:                                                           100
  Actual step count:                                                              153
  Sampling Percent:                                                               5
  Tuning Factor:                                                                  10
  Out of range Histogram Adjustment is DEFAULT.                                   

• Manually 'update index statistics <table>' without specifying 'sampling = <percent>' does not resolve the issue, Optdiag still shows the Sampling Percent used was 5 
• The ASE server configuration "enable sticky statistics" is set to 1 .

SAP Adaptive Server Enterprise (ASE) 16.0

Sybase, SAP Adaptive Server Enterprise, dropped, performance, stats, update stats, upd stats, zero, factor, config, query, sticky, stats, statistics , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Problem