Fixing frequence histogram issues

The second issue look for the Res_Text250_05 column for table ArticleLang:

Column (#13): 
NewDensity:0.000053, OldDensity:0.000000 BktCnt:9392, PopBktCnt:9375, PopValCnt:114, NDV:208
Column (#13): 
NewDensity:0.000122, OldDensity:0.000000 BktCnt:4113, PopBktCnt:4091, PopValCnt:103, NDV:208
Column (#13): Res_Text250_05( Part#: 1
AvgLen: 4 NDV: 208 Nulls: 10118996 Density: 0.000122
Histogram: Freq #Bkts: 125 UncompBkts: 4113 EndPtVals: 125
Column (#13): Res_Text250_05(
AvgLen: 4 NDV: 208 Nulls: 10118996 Density: 0.000122
Histogram: Freq #Bkts: 125 UncompBkts: 4113 EndPtVals: 125
…
…
Using density: 0.000122 of col #13 as selectivity of unpopular value pred

Issue: The value selected did not fall into any of the histogram buckets.

Solution: Add more buckets such that all values in the column can be assigned a bucket by forcing the optimizer to analyze 100 percent of the rows. As the number of distinct values in this column is less than 254 (Oracle max # of buckets) this is a suitable solution:

-- script which adds more buckets for frequency histograms
DECLARE
  BUCKETS_BUFFER NUMBER := 0;
BEGIN
  FOR COL_ITEM IN (
                    SELECT stats.OWNER, stats.TABLE_NAME, 
                    LISTAGG('FOR COLUMNS "' || stats.COLUMN_NAME || '" SIZE ' || CASE WHEN stats.NUM_DISTINCT + BUCKETS_BUFFER > 254 THEN 254 ELSE stats.NUM_DISTINCT + BUCKETS_BUFFER END, ', ') WITHIN GROUP (ORDER BY stats.COLUMN_NAME) AS METHOD_OPT
                    FROM DBA_TAB_COL_STATISTICS stats
                      INNER JOIN DBA_TAB_COLUMNS cols
                        ON stats.TABLE_NAME = cols.TABLE_NAME
                        AND stats.COLUMN_NAME = cols.COLUMN_NAME
                    WHERE cols.DATA_TYPE = 'NVARCHAR2'
                    AND stats.HISTOGRAM = 'FREQUENCY'
                    AND stats.NUM_DISTINCT > stats.NUM_BUCKETS
                    AND stats.OWNER = cols.OWNER
                    AND stats.OWNER IN ('HBC_HPM_MAIN','HBC_HPM_MASTER','HBC_HPM_SUPPLIER')
                    GROUP BY stats.OWNER, stats.TABLE_NAME
                    ORDER BY stats.OWNER, stats.TABLE_NAME
                  )
  LOOP
    DBMS_STATS.GATHER_TABLE_STATUS
    (
      OWNNAME => COL_ITEM.OWNER, 
      TABNAME => '"' || COL_ITEM.TABLE_NAME || '"', 
      ESTIMATE_PERCENT => 100,
      METHOD_OPT => COL_ITEM.METHOD_OPT,
      CASCADE => FALSE
    );
  END LOOP;
END;
/