How to run Gather Schema Statistics in R12.2

What is Gather Schema Statistics?

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.

Why Run the Gather Schema Statistics?

When Data is update, insert, delete in table of user end Like (Technical User ,Finance User ,Ect) It become necessary to run the Gather Schema Statistics

It’s recommended to Run GSS in Weekly Once or twice.

How to run Gather Schema Statistics?

Login application with sysadmin user

Submit Request Window

Navigate to: Concurrent > Requests

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

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. 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 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.

Gather table stats:

Gather table stats gather the stats for the particular table.

This concurrent program gathers index statistics for the table by default.

This program can optionally backup the existing statistics in the FND_STSTTAB table before gathering the statistics.

SQLPlus

Table Stats

EXEC FND_STATS.gather_table_stats('<owner>','<tablename>');

Schema Stats

EXEC FND_STATS.gather_schema_stats('All'); EXEC

EXEC FND_STATS.gather_schema_stats('AR'); FOR table status: Exec

 

Important tables related to Gather stats

 FND_STATS_HIST

To record the time taken for gathering the statistics for the different types of objects.

FND_HISTOGRAM_COLS

Gather Schema stats create the histogram for the specified columns in the tables.

Note:

Best practices for gathering statistics with oracle E-Business Suite (Doc ID 1586374.1)