Gather Schema Statistics Using FND_STATS in EBS 11i and R12

How to Gather statistics in EBS

• In order to use Cost Based Optimizer effectively, the schema statistics must be current. Fortunately, Oracle EBS has provided some scripts and concurrent programs(Gather Schema Statistics and Gather Table statistics) to make this administrative task easier. FND_STATS is a PL/SQL package for the Applications that has numerous functions that will assist in this administrative task. This package can be invoked either from a seeded Concurrent Program, or directly from SQL*Plus.
• It is not recommended to use DBMS_STATS with Oracle apps Database.
• When using the ‘Gather Schema Statistics’ concurrent program, it is recommended that you only pass the schema name parameter and let the other parameters default to their default values. The schema name passed can be a specific schema name (e.g. GL) or ‘ALL’ if you prefer to gather CBO stats for Apps modules. This choice uses the “estimate” method with a sample size of 10% (default).
• When using the ‘Gather Table Statistics’ concurrent program, only pass the owner of the table (schema name) and the table name. Let all other parameters default automatically, except when the table is a partitioned table.

To manually execute FND_STATS from SQL*Plus to gather CBO stats for one or all schemas, or for a particular table, use the following syntax:

To gather statistics for particular schema

exec fnd_stats.gather_schema_statistics('AP');

To gather statistics for ALL  schema in database

exec fnd_stats.gather_schema_statistics('ALL');

To gather statistics  for particular table 

exec fnd_stats.gather_table_stats('GL','GL_JE_LINES');

Some more command 

exec apps.fnd_stats.GATHER_TABLE_STATS('GL','GL_POSTING');

To gather stats at 10%

exec apps.fnd_stats.GATHER_TABLE_STATS('QP','QP_LIST_HEADERS_TL',10);

To gather stats at 30%

exec apps.fnd_stats.GATHER_TABLE_STATS('CSM','CSM_MTL_SYSTEM_ITEMS_ACC',35);

To gather stats at 25% with 4 parallel worker

exec apps.fnd_stats.GATHER_TABLE_STATS('ONT','OE_TRANSACTION_TYPES_ALL',25,4);

To gather stats with auto_sample_size

exec apps.fnd_stats.GATHER_TABLE_STATS('HR','PAY_RUN_BALANCES',dbms_stats.auto_sample_size);

Similar commands

exec apps.fnd_stats.GATHER_TABLE_STATS('HR','PAY_PAYROLL_ACTIONS',10,4);
exec apps.fnd_stats.GATHER_TABLE_STATS('HR',HR_CODES',35);
exec apps.fnd_stats.GATHER_TABLE_STATS('CS','CS_INCIDENT_TYPE_TL');
exec apps.fnd_stats.GATHER_SCHEMA_STATS('GL',25);

With 11g and 12c Database, It is recommended to start using GATHER AUTO and AUTO SAMPLE SIZE for gathering statistics

This can be done through concurrent request also or through the sql

Gather for the APPLSYS schema:

exec apps.fnd_stats.gather_schema_statistics(schemaname=> 'APPLSYS',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE ,options => 'GATHER AUTO');

Gather for ALL schemas: (may or may not want to do this)

exec apps.fnd_stats.gather_schema_statistics(schemaname=> 'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE ,options => 'GATHER AUTO');

This runs quite faster to other above ones and it is very accurate also. It is faster as it generates stats for stale objects only and that too with auto sample size

If you want to know the timing gather stats took on the schema’s, we can use below query

select owner, min(last_analyzed) , max(last_analyzed) ,( max(last_analyzed) - min(last_analyzed))*24 hours_took
from dba_tables
where last_analyzed is not null
and trunc (last_analyzed) = '&enter_date_DD-MON-YY' -- Example : '01-APR-19'
group by owner
order by hours_took ;

Gathering system statistics with 11i and R12

For E-Business Suite, for non-Exadata databases, it is recommend to gather system statistics with NOWORKLOAD option, as in:

execute DBMS_STATS.GATHER_SYSTEM_STATS (gathering_mode => 'NOWORKLOAD');

But for Exadata database use following command instead :

execute DBMS_STATS.GATHER_SYSTEM_STATS ('EXADATA');

Typically, this is only need to be done once after any major architectural changes made to the DB.

Gathering dictionary  statistics with 11i and R12

• You must have the SYSDBA (or both ANALYZE ANY DICTIONARY and ANALYZE ANY) system privilege to execute this procedure.

execute dbms_stats.gather_schema_stats(''SYS'', method_opt=>''for all columns size 1'', degree=>30,estimate_percent=>100,cascade=>true);

• If you are using Database version 10g/11g, you can use the following syntax instead

execute dbms_stats.gather_dictionary_stats ();

• This procedure will gather statistics for all system schema’s, including SYS and SYSTEM, and other optional schema’s, such as CTXSYS and DRSYS.
• Typically, this is only need to be done once after any major architectural changes made to the DB.

This concludes the steps for Gathering optimizer Statistics in Oracle EBS environment. Hope you like post on Gather Schema Statistics Using FND_STATS in EBS 11i and R12

Also Reads
Oracle concurrent Manager
Optimizer hints
https://docs.oracle.com/cd/E18727_01/doc.121/e12893/T174296T174306.htm