- Oracle has provided a procedure VERIFY_STATS in the FND_STATS package to verify stats for the table in EBS
- This procedure reports the statistics in the data dictionary tables for the tables, indexes, and histograms.
Syntax
FND_STATS.VERIFY_STATS (
schemaname VARCHAR2 DEFAULT NULL,
tablelist VARCHAR2 DEFAULT NULL,
days_old NUMBER DEFAULT NULL,
column_stat BOOLEAN DEFAULT FALSE);
To verify stats for a single table
set server output on
exec fnd_stats.verify_stats('&table_owner','&table_name');
Example
set server output on exec fnd_stats.verify_stats('APPLSYS','FND_USER');
To verify stats for multiple tables in a single schema
set server output on
exec fnd_stats.verify_stats('&table_owner','&table_name1,&table_name2');
Example
set server output on exec fnd_stats.verify_stats('APPLSYS','FND_USER,FND_COMCURRENT_REQUESTS');
To verify stats for multiple tables in a different schema
set server output on
exec fnd_stats.verify_stats(tablelist=>'owner1.table_name1,owner2.table_name2');
Example
set server output on exec fnd_stats.verify_stats(tablelist=>'APPLSYS.FND_USER,HR.PER_ALL');
I hope this article is useful in your daily administration. Please do provide the feedback
Related Articles
How to check gather stats on a table : Check out this post on How to check gather stats on a table,how to check last gather stats on the table in oracle
Gather Schema Statistics Using FND_STATS in EBS :Gather Schema Statistics and Gather Table statistics are seeded Concurrent Program in Oracle EBS to generate the optimizer stats using FND_STATS package
Histograms in EBS : check out about Histograms in EBS, How to check the columns for histograms, How to load new columns for histograms