Oracle Applications and Query Optimization:
The cost-base optimization is used in Oracle Application Release 2 version for choosing the most proficient execution plan for the SQL statement.
By using this plan the optimizer conclude the most proficient execution plan based on the costing and distributed notification statistics for the schema objects which are accessed by the SQL statement. And in oracle application there are various database initialization parameters which need to be set for optimizing performance of the SQL statements.
As per oracle System Administrator’s Guide there is a note on Oracle metalink i.e. 396009.1 which need to be referred for setting up the initialization parameters on Oracle database.
For getting the optimal execution plan from query optimizer the data dictionary statistics should be very accurate in order to volume and data of the tables and indexes. So the database statistics should always be refreshed timely.
We have seen that the systems which are going live will experience huge amount of data that need to be inserted to the new system from the legacy system which we are migrating. But in this case the statistics are needed to be refreshed very frequently because the huge amount of data will change the data distribution very significantly.
A set of procedures under FND_STATS package is provided by Oracle application to collecting the statistics. This package internally uses DBMS_STATS package to gather statistics.
Note in oracle application we should not run the DBMS_STATS directly.
GATHER_SCHEMA_STATS is one of the procedures under FND_STATS which uses a parameter option and if this is set to GATHER AUTO then it will automatically gather the statics for the tables based on the change threshold. The Threshold can also be modified by the user by passing a value for modpercent which in default is equal to 10. This GATHER AUTO internally uses one of the database features, which is called Table Monitoring which indeed needed to be enabled for all the tables. This can be done by using a procedure called “ENABLE_SCHEMA_MONITORING” which has the ability to monitor all the tables for a given schema and all the application schemas.
Statistics for the CBO (Cost based optimizer):
There are few concurrent programs provided by Oracle application which uses FND_STATS to gather the statics for oracle application database objects:
• Gather Table Statistics
• Backup Table Statistics
• Restore Table Statistics
• Gather Schema Statistics
• Purge FND_STATS History Records
To have more detail information on the above mentioned database objects , please go through ” System Administrator’s Guide – Configuration ” from technet.oracle.com