- Histograms in Oracle are required for skewed data, so they are also needed for the Oracle EBS database.
- Histograms tell the CBO when data is not evenly distributed and can help the CBO 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 in EBS
Select column_name, hsize from applsys.fnd_histogram_cols where table_name =
'&table_name'
How to load new columns for histograms
As such, the 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 use 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
exec fnd_stats.gather_table_stats('AD','AD_SYS_TABLES');
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
Related Articles
Histograms queries in Oracle
Skew in Database