We need to gather statistics after the completion of an upgradeation, installation,after loading large amout of data and high through output or large data volume in database.
Oracle Provide concurrent programs in Apps for gathering statistics.
- Gather Schema Statistics- This request gathers statistics for all objects in a schema. Before gathering the statistics, this also takes a backup of the existing statistics
- Gather Table Statistics
- Gather Column Statistics
- Gather All Column Statistics
- Analyze All Index Columns - analyzes all the indexed columns for all the tables in a given schema
- Backup Table Statistics - Backs up the statistics of all the objects of the given schema into FND_STATTAB
- Restore Table Statistics - This procedure retrieves statistics for a particular table from the FND_STATTAB
Common parameters and their meanings in All Concurrent programmes:
Schemaname
Enter the three character schema to analyze.for example: MRP or INV or ONT or ALL. You may enter ALL to analyze every defined App schema.
Estimate_percent (Default is 10)
Percentage of rows to estimate.
Degree
Enter the Degree of parallelism. If not entered, it will default to min(cpu_count, parallel_max_servers).
Internal Flag
If the value is 'NOBACKUP' then it won't take a backup of the current statistics and should run quicker. If the value is 'BACKUP' then it does an export_table_stats prior to gathering the statistics.
Restart Request Id
Enter the request id that should be used for recovering gather_schema_stats if this request should fail. You may leave.
Reference
Metalink Note: 122371.1 and 419728.1