Home » Oracle » Oracle Ebuisness Suite » Histograms in EBS

Histograms in EBS

  • 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 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 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

See also  How to clone the database using manual cold backup

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
Histograms queries in Oracle

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top