Use below articles for Gathering Stats in EBS. You can find a PDF to download.
Best Practices for Gathering Statistics with Oracle E-Business Suite (Doc ID 1586374.1)
For table stats: Exec FND_STATS.gather_table_stats(‘owner’,’table_name’);
For best results on large tables, you can achieve Auto Sampling using below:
exec fnd_stats.GATHER_TABLE_STATS( OWNNAME => 'HR', TABNAME=> 'PAY_RUN_RESULT_VALUES', PERCENT => 0, DEGREE => 4 );
You can do same from EBS Concurrent Program "Gather Schema Statistics"
Its not recommended to use DBMS_STATS for EBS. Use always FND_STATS.
Always use fnd_stats rather than dbms_stats on an EBS system; the reason being, fnd_stats does additional work such as creating specific histograms which are defined in the application.
Have you locked statistics on any table?
No, I did not, but maybe EBS software did it on previous initial setup?
They are using DBMS_STATS for Dictionary stats not related to APPS to it will not use FND_STATS in this case.
You can use below query to check which stats are locked:
SELECT TABLE_NAME FROM DBA_TAB_STATISTICS WHERE STATTYPE_LOCKED = 'ALL';
Then unlock using:
exec dbms_stats.unlock_table_stats('scott', 'test');
If needed, do a bounce of database and try to run stats while application is not running.