Scheduling Gather Schema Statistics

Scheduling Gather Schema Statistics For “ALL” Schemas.

================================================

To run concurrent program Gather Schema Statistics:

Log on to Oracle Applications withSysadmin

Select System Administrator à Concurrent à Requests

Select Single Request option as shown in the below picture ….

Click ok to continue ….

Query for “Gather Schema Statistics” concurrent program …

Select “Gather Schema Statistics” concurrent program and click ok ..

It will Request for the “Parameters” 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..

Click Ok to continue ….

Click Schedule button to schedule the gather schema statistics program as per the requirement …

Select Periodically Option for running weekly once …

Scheduled Time (Every week Friday at 20.00 Perth Time)..

Click OK …

And Submit the Concurrent Request …

Note down the Concurrent Request ID …

To Check the Concurrent Request ID status:

Checking the Concurrent Request ID status from database …..

Detailed Information about Parameters

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 of10 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 ofparallel_max_servers and cpu_count.

Backup Flag: NOBACKUP isused, 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

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