To drop a column histogram and prevent its future generation in current Oracle databases, execute DBMS_STATS.DELETE_COLUMN_STATS. Next, use DBMS_STATS.SET_TABLE_PREFS to set the METHOD_OPT preference strictly to SIZE 1 for that specific column. This safely instructs the Cost-Based Optimizer to permanently bypass histogram creation during automated statistics gathering

Sometimes you want to drop the histogram of a particular table and dont want to generate a histogram for a particular column in the table. let’s see how we can achieve it

11G and the above solution

First of all, you can delete the existing histograms using the below command

BEGIN
dbms_stats.delete_column_stats(
ownname=>'SCOTT', tabname=>'TEST', colname=>'UNIQ_ID', col_stat_type=>'HISTOGRAM');
END;

Now you can use the new dbms_stats.set_table_pref procedure to set a specific value for the method_opt parameter for the table affected by this problem. You can achieve this using the below command

BEGIN
dbms_stats.set_table_prefs('SCOTT', 'EMP','METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS   SIZE 1 UNIQ_ID');
END;
/

Now the statistics gathering commands will now use the table preference you set when it gathers statistics on this table and will no longer create a histogram on the particular column

10G solution

You should begin by dropping the existing statistics for the sales table. You can do this by using the dbms_stats.delete_table_stats procedure.

BEGIN
dbms_stats.delete_table_stats(ownname=>'SCOTT', tabname=>'TEST');
END;
/

Now. we dont have preferences in 10g, so we need to lock the statistics on the affected table using dbms_stats.lock_table_stats.

BEGIN
dbms_stats.lock_table_stats(ownname=>'SCOTT', tabname=>'TEST');
END;
/

Now your scheduled gather stats will not gather stats on this table. Now you can put another script to generate the stats on this table

BEGIN
dbms_stats.gather_table_stats('SCOTT', 'TEST',
method_opt=>'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 1 UNIQ_ID',
force => true);
END;
/

Here force option will let the command run the gather stats on this table.

How to check if the column is having histograms

select COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,HISTOGRAM from DBA_TAB_COL_STATISTICS where table_name='&table_name' and owner='&table_owner' order by column_name;

Histograms can be a powerful tool for improving query performance in Oracle Database, but they should be used judiciously. It’s important to monitor the performance of queries and adjust histogram settings as needed to achieve optimal performance.

How to Identify When an Oracle Histogram is Hurting Query Performance

An Oracle histogram is hurting query performance when it causes the optimizer to choose a plan that is worse than the plan it would have picked without the histogram (for example, a full‑table scan instead of an index access, or a bad join order). You can usually detect this by comparing plans and execution times with and without histograms on the affected columns.

Symptoms to watch for Common signs that a histogram is degrading performance include:

• Query suddenly regresses after a statistics‑gather job (same SQL runs much slower post‑dbms_stats).
• Plan change (different plan_hash_value) for the same SQL, especially when histograms are newly created or modified.
• Worse access path, e.g., the optimizer stops using a selective index and switches to a full‑table scan or a less efficient join method.
• Inconsistent behavior between environments (e.g., test vs production) where only the histogram configuration differs.

How to confirm a histogram is the culprit

a) Check which columns have histograms

SELECT column_name, histogram, num_buckets
FROM user_tab_col_statistics
WHERE table_name = 'YOUR_TABLE';
If the predicate column of your slow query has a histogram (especially FREQUENCY or HYBRID), suspect it.

b) Compare plans with and without the histogram
Gather statistics without histograms on the suspect column:

EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'SCHEMA',
tabname => 'YOUR_TABLE',
method_opt => 'FOR ALL COLUMNS SIZE 1',
cascade => TRUE
);

Re‑run the query and capture its plan (e.g., from V$SQL_PLAN or EXPLAIN PLAN).

Restore the histogram (or use a test environment) and repeat.
If the plan improves significantly without the histogram, the histogram is likely harming that query.

c) Examine cardinality estimates
Use EXPLAIN PLAN or a SQL‑trace to see the optimizer’s estimated cardinality at each step:

If the histogram causes large over‑/under‑estimates of rows (e.g., expected 1 row but actually 100K), the join order or access path can be suboptimal.

Tools like SQL Monitor or DBMS_XPLAN.DISPLAY with ALLSTATS LAST show actual vs estimated rows; a big mismatch often points to a bad histogram‑driven estimat

d) Compare environment differences
If the same query runs fast in one environment and slow in another, check:

Whether histograms exist on the same columns.

Often the only difference is the presence or type of histograms, and removing them in the “slow” environment restores performance.

Related Articles

Understanding Bind Variable Peeking and Plan Instability
How to Lock Table Statistics in Oracle Database