Gather Schema Statistics

This concurrent program gathers the specified schema level statistics

This program also creates histograms on the columns seeded in the FND_HISTOGRAM_COLS table

For a detailed description of the procedure used by this concurrent program, see information on the GATHER_SCHEMA_STATS procedure

Parameters

Schema Name

Schema for which statistics are to be gathered. Specify ALL for all Oracle E-Business Suite schemas (all schemas that have an entry in the FND_PRODUCT_INSTALLATIONS table).

Percent

The sampling percentage. If left blank, the default value of 10 is used. The valid range is from 0 to 100.

Degree

The degree of parallelism to be used for gathering statistics. If a Degree is not provided, it defaults to the minimum of parallel_max_servers and cpu_count

Backup Flag

The backup flag indicates whether to backup statistics. Set this flag to BACKUP if you wish to back up the current statistics into the FND_STATTAB table. If NOBACKUP is used, then the GATHER_SCHEMA_STATS procedure will not backup the current statistics. This way the GATHER_SCHEMA_STATS procedure will run faster.

Restart Request ID

In the case where the Gather Schema Statistics run fails due to whatever reasons, the concurrent request can be re-submitted and it will pick up where the failed run left off, if you provide the concurrent request_id of the failed run.

History Mod

This parameter controls the amount of history records that are created. The history records, stored in FND_STATS_HIST can be queried to find out when stats were gathered on a particular object and the amount of time it took to gather statistics on that object.

• Last Run – History records for each object are maintained only for the last gather statistics run. Each subsequent run will overwrite the previous history record for the object. This is the default behavior.
• Full – This mode does not overwrite any history information. History records are created for each run and are identified by the Request ID. If a Request ID is not provided, one is generated automatically. If this mode is used, the “Purge FND_STATS History Records ” concurrent program should be run periodically to purge the FND_STATS_HIST table.
• None – This mode does not generate any history information. If this mode is used, the run cannot be restarted.

Gather Options

This parameter specifies how objects are selected for statistics gathering.

• GATHER : All tables and indexes of the schema schemaname are selected for stats gathering. This is the default.
• GATHER AUTO : Tables of the schema schemaname for which the percentage of modifications has exceeded modpercent are selected for statistics gathering. Indexes of these tables are selected by default. Table monitoring needs to be enabled before using this option.
• GATHER EMPTY : Statistics are gathered only for tables and indexes that are missing statistics.
• LIST AUTO : This option does not gather statistics. It only provides a listing of all the tables that will be selected for statistic gathering, if the GATHER AUTO option is used.

• LIST EMPTY : This option does not gather statistics. It only provides a listing of all the tables that will be selected for statistics gathering, if the GATHER EMPTY option is used

Modifications Threshold

Applicable only to GATHER AUTO and LIST AUTO options. This parameter specifies the percentage of modifications (with respect to the total rows) that have to take place on a table before it can be picked up for AUTO statistics gathering.

Invalidate Dependent Cursors

This flag indicates whether cursors dependent on the table being analyzed should be invalidated or not. By default, dependent cursors are invalidated. This parameter is ignored if you are running a database prior to Oracle 9i Release 2 (9.2.x).