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