Gathering Statistics in Release 11i and R12

Last updated on December 21st, 2015 at 08:00 am

In order to use CBO effectively, the statistics must be current. Fortunately, Oracle has provided some scripts and concurrent programs 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. The following concurrent programs have been defined to gather and maintain various statistics using the FND_STATS package
When using the ‘Gathering 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%

To gather stats at 25% with 4 parallel worker

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_SCHEMA_STATS(‘GL’,25);

Leave a Reply