Gather Schema Stats (Oracle Apps)

Gather Schema Statistics (Oracle Applications)

Gather Schema Statistics program generates statistics that quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions.

The cost-based optimization (CBO) uses these statistics to calculate the selectivity of prediction and to estimate the cost of each execution plan.

We run Gather Schema Statistics under the following circumstances:

1. There is a Significant change in data in either content or volume.
2. After importing data.
3. End-users notice deterioration in performance in routine day-to-day business transactions
4. When running concurrent programs is taking longer than usual.
5. Run on a regular basis (weekly at a minimum) and any time after application of patch, conversion, etc.
6. By default GSS will not run on Custom schema

Estimate Percentage / Modification threshold defines the percentage which should be used to initiate gather stats for those objects which have actually changed beyond the threshold.
The default is 10% (any table which has changed via DML more than 10%, stats will be collected, otherwise it will be skipped).
ow to run Gather Schema Statistics concurrent program:

1.     Log on to Oracle Applications with

Responsibility = System Administrator

2. Submit Request Window
Navigate to: Concurrent > Requests

3. Query for the Gather Schema Statistics

4. Enter the appropriate parameters. This can be run for specific schemas by specifying the schema name or entering ‘ALL’ to gather statistics for every schema in the database

5. Submit the Gather Schema Statistics program

Parameters :
——————
Schema Name: Schema for which statistics are to be gathered. Specify ALL for all Oracle Applications schemas
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: 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 Mode: 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 behaviour
Gather Options: All tables and indexes of the schema schema name are selected for stats gathering. This is the default
Modifications Threshold: Applicable only to GATHER AUTO and LIST AUTO Options
Invalidate Dependent Cursors: This flag indicates whether cursors dependent on the table being analysed should be invalidated or not. By default, dependent cursors are invalidated.

Gather statistics in Oracle apps from backend

Use the following command to gather schema statistics:

exec fnd_stats.gather_schema_statistics('ONT') < For a specific schema >
exec fnd_stats.gather_schema_statistics('ALL') < For all schemas >