- Histograms in Oracle are required for skewed data, so it is required in the Oracle EBS database also.
- Histograms tell the CBO when data is not evenly distributed and can help the CBO to estimate the number of rows returned from a table join (called “cardinality”). Having histograms on the skewed columns may aid the optimizer in making a proper decision.
How the histograms are generated in EBS
Oracle product team decides about the column where histograms are needed and those columns are inserted into the table FND_HISTOGRAM_COLS.
FND_STATS collects histogram information if a column is listed in FND_HISTOGRAM_COLS.
we should not use DBMS_STATS to gather stats in the EBS database as then it will create histograms of many columns where it may not be needed
How to check the columns for histograms
Select column_name, hsize from applsys.fnd_histogram_cols where table_name =
How to load new columns for histograms
As such oracle product team takes care of these for the standard table, but if it is required to add a column to the standard table or columns for custom tables, then we should the procedure FND_STATS.LOAD_HISTOGRAM_COLS
execute fnd_stats.load_histogram_cols(action => 'INSERT', appl_id => '<app id>', tabname => '<table name>', colname => '<col name>', hsize => '<size>'); Example: exec fnd_stats.load_histogram_cols(action => 'INSERT', appl_id => 100, tabname => 'AD_SYS_TABLES', colname => 'LAST_UPDATED_BY', hsize => '150');
It is not supported to manually insert columns in the table FND_HISTOGRAM_COLS
You should gather stats for the table after loading the columns to generate histograms for the new columns
How to check for the histograms in the table
select * from dba_tab_histograms where table_name = '&table_name' and owner = '&table_owner';
How does fnd_stats generate the histograms
- FND_STATS internally calls DBMS_STATS procedure to gather the statistics
- It uses FND_HISTOGRAM_COLS to generate the method_opt for the table and then generates the statistics using the DBMS_STATS procedure
- if there is no column in FND_HISTOGRAM_COLS, it uses method_opt as ‘ for all columns size 1’
- if there are column in FND_HISTOGRAM_COLS, it uses method_opt as ‘ for all columns size 1 for column <col name> size <hsize>’
I hope you like this article on Histograms in EBS
Histograms queries in Oracle
Skew in Database
Leave a Reply