1. Log on to Oracle Applications with
Responsibility = System Administrator2. Submit Request Window
Navigate to: Concurrent > Requests3. Query for the Gather Schema Statistics4. 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 database5. Submit the Gather Schema Statistics programParameters :
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: NO BACKUP 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 behavior
Gather Options:
GATHER: All tables and indexes of the schema schema name are selected for stats gathering. This is the default
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
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.