Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com
http://www.free-online-exams.com
Problem:
How To Gather Statistics On Oracle
Applications 11.5.10(and above)
Solution:
To run concurrent
program Gather Schema Statistics:
1. Log on to Oracle
Applications with
Responsibility = System Administrator
2. Submit Request Window
Navigate to: List > Request > Run.
3. 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.
4. Submit the gather schema statistics program.
Responsibility = System Administrator
2. Submit Request Window
Navigate to: List > Request > Run.
3. 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.
4. Submit the gather schema statistics program.
Oracle Applications
provides a set of procedures in the FND_STATS package to facilitate collection
of these statistics. FND_STATS uses the DBMS_STATS package to gather
statistics.
The following concurrent requests are available in Oracle Applications for gathering statistics:
Gather All Column Statistics
Gather Column Statistics
Gather Schema Statistics
Gather Table Statistics
For Oracle Applications 11i it is recommended to use only the 'Gather Schema Statistics' or the 'Gather Table Statistics'. Please refer to the Oracle Application System Administration Guide for concurrent request parameters. Here are common parameters and their meanings:
Schemaname
You may enter ALL to analyze every defined App schema.
Estimate_percent
Percentage of rows to estimate. If left empty it will default to 10%. The valid range is 0-99. A higher percentage will be more accurate, but take longer to run. If the object(s) that you are gathering statistics for do not change often or the object(s) has data entered that is very similiar you may choose a lower number. However, if the data changes frequently a larger number entered for this parameter would be recommended to provide a more accurate representation of your data.
Degree
Enter the Degree of parallelism. If not entered, it will default to min(cpu_count, parallel_max_servers). Modifying the degree of parallelism on a table can cause the plan to change. Increasing the degree of parallelism is likely to make full table scans appear cheaper and more attractive while reducing it will make Full Table Scans look less attractive.
Backup 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
this parameter null.
Gather Options
As of 11.5.10, FND_STATS.GATHER_SCHEMA_STATS introduced a new parameter called OPTIONS that, if set to GATHER AUTO, allows FND_STATS to automatically determine the tables for which statistics should be gathered based on the change threshold. The Modifications Threshold can be adjusted by the user by passing a value for modpercent, which by default is equal to 10. GATHER AUTO uses a database feature called Table Monitoring, which needs to be enabled for all the tables. A procedure called ENABLE_SCHEMA_MONITORING has been provided to enable monitoring on all tables for a given schema or all Applications schemas .
The following concurrent requests are available in Oracle Applications for gathering statistics:
Gather All Column Statistics
Gather Column Statistics
Gather Schema Statistics
Gather Table Statistics
For Oracle Applications 11i it is recommended to use only the 'Gather Schema Statistics' or the 'Gather Table Statistics'. Please refer to the Oracle Application System Administration Guide for concurrent request parameters. Here are common parameters and their meanings:
Schemaname
You may enter ALL to analyze every defined App schema.
Estimate_percent
Percentage of rows to estimate. If left empty it will default to 10%. The valid range is 0-99. A higher percentage will be more accurate, but take longer to run. If the object(s) that you are gathering statistics for do not change often or the object(s) has data entered that is very similiar you may choose a lower number. However, if the data changes frequently a larger number entered for this parameter would be recommended to provide a more accurate representation of your data.
Degree
Enter the Degree of parallelism. If not entered, it will default to min(cpu_count, parallel_max_servers). Modifying the degree of parallelism on a table can cause the plan to change. Increasing the degree of parallelism is likely to make full table scans appear cheaper and more attractive while reducing it will make Full Table Scans look less attractive.
Backup 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
this parameter null.
Gather Options
As of 11.5.10, FND_STATS.GATHER_SCHEMA_STATS introduced a new parameter called OPTIONS that, if set to GATHER AUTO, allows FND_STATS to automatically determine the tables for which statistics should be gathered based on the change threshold. The Modifications Threshold can be adjusted by the user by passing a value for modpercent, which by default is equal to 10. GATHER AUTO uses a database feature called Table Monitoring, which needs to be enabled for all the tables. A procedure called ENABLE_SCHEMA_MONITORING has been provided to enable monitoring on all tables for a given schema or all Applications schemas .
In R11i customers
should be using the FND_STATS command.
Do not use the ANALYZE command or DBMS_STATS package directly,
as doing so may cause incomplete statistics to be generated.
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 >
Use the following command for gathering statistics on a temporary table
(ie: temporary tablename = TEMP_tmp in schema ABC):
exec fnd_stats.gather_table_stats('ABC','TEMP_tmp');
Gathering statistics after population for QP(Advanced Pricing) Interface tables:
Reference: Note 394687.1 - Bulk Load Price List Open Interface - Slow Performance
Do not use the ANALYZE command or DBMS_STATS package directly,
as doing so may cause incomplete statistics to be generated.
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 >
Use the following command for gathering statistics on a temporary table
(ie: temporary tablename = TEMP_tmp in schema ABC):
exec fnd_stats.gather_table_stats('ABC','TEMP_tmp');
Gathering statistics after population for QP(Advanced Pricing) Interface tables:
Reference: Note 394687.1 - Bulk Load Price List Open Interface - Slow Performance
References:
Note.419728.1 Ext/Mod How To Gather Statistics On
Oracle Applications 11.5.10 - Concurrent Process,Temp Tables,
Manually
No comments:
Post a Comment