Gathering Statistics

---

Remember, you are dealing with the CBO. If you have migrated from an earlier version of Apps, this transition may be a big one to make. Oracle strongly suggests that, since you are using Oracle E-Business Suite, you use the fnd_stats package to gather your statistics on your system. You can invoke this package indirectly through Concurrent Programs like Gather Schema Statistics or Gather Table Statistics. Gather Schema Statistics takes one parameter: the schema name on which you want to gather the statistics. All is a valid parameter, but depending on the size of your data and the number of products in which you have larger amounts of data stored, using this parameter may cause the process to run for several hours. You need to make sure that, when you are running something of this magnitude, you set it to run when there is a minimum of users accessing the system. Gather Table Statistics takes the schema name of the owner of the table as well as the table name on which you want to gather the statistics. Optionally, if you have a partitioned table that you want to gather statistics on in this manner, you will want to pass in the PARTITION parameter as the granularity. This will cause the package to not only calculate the global statistics on the entire table, but the statistics on each partition as well.

Alternatively, you can script the running of these packaged procedures from the SQL prompt to remove the extra load from the Concurrent Managers.

To gather statistics on the entire GL schema:

 Exec fnd_stats.gather_schema_statistics ('GL'); 

To gather schema statistics on the entire system (except the SYS and SYSTEM tables):

 Exec fnd_stats.gather_schema_statistics ('ALL'); 

To gather table statistics on the FND_PROFILE_OPTION_VALUES table:

 Exec fnd_stats.gather_table_stats ('APPLSYS', FND_PROFILE_OPTION_VALUES'); 

To gather table statistics on a partitioned table:

 Exec fnd_stats.gather_table_stats (owner=> '<table owner>', tablename => '<partitioned table>', granularity=> 'PARTITION'); 

It is important to remember that if you have never run any statistics gathering processes on a given object (i.e., table, index, schema), the CBO makes certain assumptions. Primarily, CBO assumes that if you have not gathered statistics that you want to use the default statistics. You probably do not.

There is a freely available script that will assist you in automating statistics gathering if you have a Metalink account and you wish to make use of it. The coe_stats.sql script is included in and well documented by Note 156968.1 from Metalink. This script is for use with Oracle E-Business Suite as it makes extensive use of the FND_STATS package procedures. It dynamically, based on table size, changes the estimation percent that it uses (a higher percent for smaller tables, a smaller percent for larger ones), which can allow you to limit the overall runtime of the statistics gathering without causing you to sacrifice accuracy. When the need to limit the size of the statistics gathering window is an issue (e.g., on a 24/7 implementation where users from all over the globe are accessing the system at any given time), using coe_stats.sql will limit the overall impact to the system and to users.

If you want to get a good overall idea of where your system stands in reference to statistics and how the CBO views them, you can download from Metalink another script (bde_last_analyzed.sql from Note 163208.1) that will summarize by schema name and date when and from where at least one or all schemas had their statistics gathered. It will further warn you of statistics that have been gathered on objects owned by SYS, of stale statistics, or on statistics gathered on partitioned tables where the global statistics and the partition level statistics are no longer in sync with each other. It provides a summary page at the beginning of the report that it produces that gives you a quick, concise overview of your system as well as a more detailed listing following.

---