Sunday, January 9, 2011

Gather Schema Statistics Using FND_STATS.

Gather Schema Statistics Using FND_STATS.

1. Gathers specified schema level statistics

2. Backs up existing statistics

3. Creates the histogram for the specified columns in the FND_HISTOGRAM_COLS tables
4. Populates default statistics for all the interface tables as defined in the FND_EXCLUDE_TABLE_STATS table.

5. This procedure (FND_STATS.GATHER_SCHEMA_STATS) gathers statistics for all objects in a schema.

6. This procedure is also available through the concurrent program "Gather Schema Statistics".

7. If this procedure fails at any time during operation, it can be restarted by supplying the request ID for the request that failed. The request ID can be captured when the program is started from concurrent manager.

Usage :

Gather Schema Statistics is Concurrent Program which can be scheduled From Oracle Applications.

The usual suggestion is to schedule it to run for every week.

It does the following:

Periodically generate statistics and histograms for your database so that the Cost Based Optimizer functions properly.

The frequency is based upon your specific usage of Oracle Applications and is different for every customer.

The AOL-supplied package FND_STATS provides a mechanism for you together statistics for the database objects.

It also provides a way for storing the current statistics in a table(FND_STATTAB) and restoring them back. This package facilitates the gathering of some statistics in parallel.

This package is basically a wrapper on top of DBMS_STATS. FND_STATS also populates.

FND_STATS_HIST to record the time taken for gathering the statistics for the different types of objects

Ref : - Oracle Application sysadmin guide

How do you gather the statistics necessary for CBO?

In R11i the FND_STATS package is used to generate statistics on the database.

FND_STATS is a PL/SQL wrapper around the DBMS_STATS package, which issues the ANALYZE command.

However, you should not use the ANALYZE command or DBMS_STATS package directly.

Doing so may result in incomplete statistics being generated.

Ref Metalink Note : 113573.1

How can you tell whether the database objects were analyzed?
Use the Verify Stats report to determine whether the current statistics are accurate.

This report is a utility provided with FND_STATS, and can be run as follows:

SQL> set server output on

SQL> set long 10000

SQL> exec fnd_stats.verify_stats('schema', 'object_name');


  1. This is really very good and informative post


  2. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer
    LinkedIn profile -
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at | +91 - 9581017828.