HYBRID histograms

Oracle has introduced HYBRID histograms with Oracle 12C.  When number of buckets are more than 254, some almost popular values may get lost , resulting in sub-optimal index usage.

A single bucket can now store popularity as well , effectively increasing number of buckets without actually increasing it.

With oracle 12C, oracle merged Frequency and Height balanced histograms into hybrid histograms(Though original one also co-exists for their original purpose).

If we don’t specify AUTO_SAMPLE_SIZE(but use user defined sampling) as estimate, Hybrid histograms will not be created.If users specify their own percentage, then the database creates frequency or height-balanced histograms.

So when in doubt that issue is due to Hybrid histograms, we can try this approach. 

we can also set hybrid histgrams of by changing preferences.

TOP FREQUENCY is also a concern since it ignores the least occurred values and may generate wrong plan. It’s good to set this one also OFF.

BEGIN

  dbms_stats.set_global_prefs(pname => 'ENABLE_HYBRID_HISTOGRAMS', pvalue => 0);

  dbms_stats.set_global_prefs(pname => 'ENABLE_TOP_FREQ_HISTOGRAMS', pvalue => 0);

END;