Understanding Histograms in Oracle and How to Manage Them

1. What is a Histogram?

A histogram in Oracle is metadata that stores the distribution of values in a column.
Optimizer uses histograms to make better cardinality estimates for queries with skewed data.
However, sometimes unwanted histograms get created, leading to bad plans and performance regression.

2. Why do histograms appear suddenly?

Because of METHOD_OPT default setting (FOR ALL COLUMNS SIZE AUTO) in DBMS_STATS or FND_STATS.
Oracle automatically decides whether to create histograms if it detects skew.
If stats were gathered with AUTO_SAMPLE_SIZE or with SIZE SKEWONLY.
If stats gathering job was changed at schema or DB-level (new preference applied).

3. How to Check Histograms on a Table

Check which columns have histograms:


SELECT column_name, histogram, num_buckets
FROM   dba_tab_col_statistics
WHERE  owner = 'SALES'
AND    table_name = 'SALES_DATA';

Output example

NONE → no histogram
HEIGHT BALANCED or HYBRID → histogram exists

4. How to Compare Histogram Changes Over Time

If you have AWR / DBA_HIST data, you can track changes:


SELECT owner, table_name, column_name, histogram, num_buckets,
last_analyzed
FROM dba_tab_col_statistics
WHERE owner = 'SALES'
AND table_name = 'SALES_DATA'
ORDER BY column_name, last_analyzed;

 

This helps you see when histograms were introduced.

Check actual histogram buckets

SELECT table_name, column_name, count(*) AS buckets, 
MIN(endpoint_number) min_val, 
MAX(endpoint_number) max_val
FROM dba_tab_histograms
WHERE table_name = 'SALES_DATA'
 AND column_name = 'SERIAL'
GROUP BY table_name, column_name;

 If buckets > 1, then Oracle built a histogram.
 If earlier there were 1 bucket and now 254 buckets → histograms changed.

5. How to Remove a Histogram (Fix Bad Plans)

To remove a histogram from a column:


EXEC DBMS_STATS.DELETE_COLUMN_STATS(
  ownname => 'SALES',
  tabname => 'SALES_DATA',
  colname => 'CUSTOMER_ID'
);

This deletes column stats including the histogram.

6. How to Prevent Histograms During Stats Gathering

Option A – For a specific table


EXEC DBMS_STATS.SET_TABLE_PREFS(
  ownname     => 'SALES',
  tabname     => 'SALES_DATA',
  pname       => 'METHOD_OPT',
  pvalue      => 'FOR ALL COLUMNS SIZE 1'
);

This ensures no histograms will be created.

Option B – For the whole schema


EXEC DBMS_STATS.SET_SCHEMA_PREFS(
  ownname => 'SALES',
  pname   => 'METHOD_OPT',
  pvalue  => 'FOR ALL COLUMNS SIZE 1'
);

Option C – For the whole database


EXEC DBMS_STATS.SET_GLOBAL_PREFS(
  pname  => 'METHOD_OPT',
  pvalue => 'FOR ALL COLUMNS SIZE 1'
);

7. How to Check Preferences (Table / Schema / DB)

Check table-level preferences:


SELECT preference_name,
 DBMS_STATS.GET_PREFS(preference_name, 'SALES', 'SALES_DATA') 
AS value
FROM (SELECT 'METHOD_OPT' FROM dual
 UNION ALL SELECT 'ESTIMATE_PERCENT' FROM dual
 UNION ALL SELECT 'DEGREE' FROM dual
 UNION ALL SELECT 'CASCADE' FROM dual
 UNION ALL SELECT 'NO_INVALIDATE' FROM dual
 UNION ALL SELECT 'GRANULARITY' FROM dual
 UNION ALL SELECT 'PUBLISH' FROM dual
 UNION ALL SELECT 'STALE_PERCENT' FROM dual);

 

Check schema-level preferences:


SELECT preference_name,
 DBMS_STATS.GET_PREFS(preference_name, 'SALES') AS value
FROM (SELECT 'METHOD_OPT' FROM dual
 UNION ALL SELECT 'ESTIMATE_PERCENT' FROM dual
 UNION ALL SELECT 'DEGREE' FROM dual
 UNION ALL SELECT 'CASCADE' FROM dual
 UNION ALL SELECT 'NO_INVALIDATE' FROM dual
 UNION ALL SELECT 'GRANULARITY' FROM dual
 UNION ALL SELECT 'PUBLISH' FROM dual
 UNION ALL SELECT 'STALE_PERCENT' FROM dual);

 

Check database-level preferences:


SELECT preference_name,
       DBMS_STATS.GET_PREFS(preference_name) AS db_value
FROM   (SELECT 'METHOD_OPT' FROM dual
        UNION ALL SELECT 'ESTIMATE_PERCENT' FROM dual
        UNION ALL SELECT 'DEGREE' FROM dual
        UNION ALL SELECT 'CASCADE' FROM dual
        UNION ALL SELECT 'NO_INVALIDATE' FROM dual
        UNION ALL SELECT 'GRANULARITY' FROM dual
        UNION ALL SELECT 'PUBLISH' FROM dual
        UNION ALL SELECT 'STALE_PERCENT' FROM dual);

8. Best Practices

Use SIZE 1 for columns that don’t need histograms.
For skewed columns that matter, manually decide histogram size.
After fixing, lock stats:

EXEC DBMS_STATS.LOCK_TABLE_STATS('SALES', 'SALES_DATA');

In Oracle EBS environments, always prefer FND_STATS instead of DBMS_STATS.

With these queries, you can detect, compare, delete, and prevent histograms.
Controlling histograms avoids sudden query plan changes and ensures stability.

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Understanding Histograms in Oracle and How to Manage Them