Top Histograms queries in Oracle

Here are some frequently used queries for histograms in Oracle

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;

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');

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