Top Histograms queries in Oracle

To manage histograms in an Oracle database, use the DBMS_STATS package. You can generate histograms by setting the method_opt parameter in GATHER_TABLE_STATS (e.g., size auto). To check existing histogram types—such as Frequency, Top-Frequency, or Hybrid—query the HISTOGRAM column in the DBA_TAB_COL_STATISTICS data dictionary view.

The Cost-Based Optimizer (CBO) uses Histograms to understand data skew. These histograms are created by the DBMS_STATS package and stored in Data Dictionary Views.Here are some frequently used queries for histograms in Oracle.

How the Oracle Cost-Based Optimizer (CBO) Uses Histograms to Fix Bad Execution Plans

The Oracle Cost-Based Optimizer (CBO) uses histograms to identify nonuniform data distribution (data skew) within a column, allowing it to generate accurate cardinality estimates (number of rows) instead of relying on average
Without histograms, the optimizer assumes data is evenly spread across a column. If a query filters on a value that appears in 95% of rows, but the CBO assumes 1%, it might incorrectly choose an index scan, leading to massive performance hits

How to check the endpoint values for the histograms

SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE, COLUMN_NAME
FROM DBA_HISTOGRAMS
WHERE TABLE_NAME='&table_name'
AND owner='&owner';

How to check the type of histogram created

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;

This will return values like FREQUENCY, TOP-FREQUENCY, HEIGHT BALANCED, or HYBRID as type of Histograms

How to check details for the frequency histograms

select
endpoint_number,endpoint_number - nvl(prev_endpoint,0) frequency,
hex_val,
chr(to_number(substr(hex_val, 2,2),'XX')) ||
chr(to_number(substr(hex_val, 4,2),'XX')) ||
chr(to_number(substr(hex_val, 6,2),'XX')) ||
chr(to_number(substr(hex_val, 8,2),'XX')) ||
chr(to_number(substr(hex_val,10,2),'XX')) ||
chr(to_number(substr(hex_val,12,2),'XX')),
endpoint_actual_value
from (
select
endpoint_number,
lag(endpoint_number,1) over(
order by endpoint_number
) prev_endpoint,
to_char(endpoint_value,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')hex_val,
endpoint_actual_value
from
dba_tab_histograms
where
owner = '&owner'
and table_name = '&table_name'
and column_name = '&column_name'
)
order by
endpoint_number
;

How to generate histograms for a table

For index columns only with auto size
exec dbms_stats.gather_table_stats( SCOTT, 'TEST', method_opt=>'for all
indexed columns size auto');
For all columns only with auto size
exec dbms_stats.gather_table_stats( SCOTT, 'TEST', method_opt=>'for all
columns size auto');
for particular Column
exec dbms_stats.gather_table_stats( SCOTT, 'TEST', method_opt=>'for 
column PROD_ID size 254');

How to delete the Histograms from the table

exec dbms_stats.gather_table_stats( SCOTT, 'TEST', method_opt=>'for all columns size 1');

How to delete the histograms for a column

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

How to check the default settings for Histograms Generation

AUTO: Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
REPEAT: Collects histograms only on the columns that already have histograms.
SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.

select dbms_stats.get_prefs ('METHOD_OPT') from dual;

DBMS_STATS.GET_PREFS('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO

TO CHANGE YOUR SETTING TO REPEAT

SQL> exec dbms_stats.set_global_prefs ('METHOD_OPT','FOR ALL COLUMNS SIZE REPEAT');
 
TO DISABLE THE HISTOGRAM COLLECTION

SQL> exec dbms_stats.set_global_prefs ('METHOD_OPT','FOR ALL COLUMNS SIZE 1');

I hope you like this article on Histograms queries in Oracle.

Related Articles

Optimizer Mode
Gathering Statistics in Release 11i and R12
What is a Bind Variable
How are the histograms generated in Oracle
Histograms in EBS