The Triora Group

What do you know about the E-Business Suite and statistics? Come with us for an inside view of gathering statistics in Release 11i. One of the areas we like to look at when doing performance tuning for Release 11i systems is to review our clients’ approach to gathering database statistics. Now, gathering statistics can spark some interesting conversations, but it is not my intent, for this article at least, to cover how often statistics should be gathered, but rather to discuss some of the more interesting finer points regarding statistics and Release 11i, including why analyzing statistics for the Applications uses a different package than if you were gathering statistics for a non-E-Business Suite environment. For more details, take a look at the resources listed at the end of this article. Read on, though, because I think I’ve discovered at least one “finer point” that most of you won’t have seen before, and that could prove very handy.

So, we are left to explore the finer points of statistics gathering as they specifically relate to Release 11i systems. I must admit I still have some unfinished research for Release 12’s take on this topic, but I’ll write another article when I’m done to compare the two releases.

Oracle provides the Release 11i E-Business Suite with a very specific way to generate statistics. This mechanism is a concurrent program named “Gather Schema Statistics” whose short name is “FNDGSCST”. This concurrent program belongs to the Application Object Library and is assigned to the System Administrator responsibility. Inspecting this program reveals that its heart is a PL\SQL stored procedure named FND_STATS.GATHER_SCHEMA_STATS. Now this is important because most DBAs are familiar with using the “analyze” command and/or the DBMS_STATS package for statistics gathering. “Analyze” was the command that we all used in the early days to populate the metadata about objects in our Oracle databases. Then, Oracle released a newer version for us to use called DBMS_STATS. Rather than being a command, we now had a stored procedure that improved on certain aspects of the analyze command. Interestingly enough, the FND_STATS package actually uses the DBMS_STATS package. Confused yet? If that is true, why do we use FND_STATS in Release 11i?

The FND_STATS package contains procedures required for the E-Business Suite. For example, FND_STATS references a table named FND_EXCLUDE_TABLE_STATS that is a repository for Application Interface Tables (AITs). The AITs are the tables that Oracle did NOT want statistics gathered on. This is important knowledge because although Oracle no longer skips Application Interface Tables we now have a structure we can use to programmatically “ignore” objects we do not want statistics gathered on. If your environment uses External Tables, you may want to insert those table names into the FND_EXCLUDE_TABLE_STATS table. One caveat to this approach is that any table you insert into the FND_EXCLUDE_TABLE_STATS table must be associated with an Applications module, because the APPLICATION_ID column is a driving column on the table. In addition, you need to know that adding a “-“ (hyphen) to the beginning of an application id will cause FND_STATS to pick up the table for statistics gathering. So, a row with an application id of “20003” will be ignored, while a row with “-20003” will have statistics gathered on it. Is that all? Nope… Read on.

Another reason for using the FND_STATS package with the E-Business Suite is the FND_HISTOGRAM_COLS table. This table contains the names of columns that are susceptible to “skewed” data. Skew happens when the distribution of data is not equal. The Cost Based Optimizer needs histograms on these columns to provide the best access path to the data. The system we used when researching FND_STATS had 1,856 rows in FND_HISTOGRAM_COLS with none being associated with a custom table.

Is that all? Nope… There are a few more interesting tidbits. Let’s move away from the package and look at some of the inside information about the concurrent program. Why is it that custom schemas do not get picked up with the gathering schema statistics concurrent program? Why don’t SYS or SYSTEM objects get analyzed? The Gather Schema Statistics concurrent program uses a Value Set named FND_STATS_OWNER_ALL to determine the schemas eligible for selection within the program. The code behind this value set also provides for an “ALL” selection, which will cause statistics to be gathered for all schemas in the FND_ORACLE_USERID table.

The specific code for this is:

SELECT ‘All’ oracle_username, ‘All Schemas’ description, 1 seq FROM DUAL UNION SELECT oracle_username, description, 2 seq FROM fnd_oracle_userid ORDER BY seq ASC, oracle_username ASC;

Based on this select statement, we know that if your custom schema is not registered, then the Gather Schema Statistics concurrent program will not pick it up. There are several different ways to register your custom schema, so make sure you review the System Administrator guides for specific instructions. In the end, there are three tables your custom schema should be listed in:

• FND_ORACLE_USERID
• FND_APPLICATION
• FND_PRODUCT_INSTALLATIONS

Yes, you can register the SYSTEM and SYS schemas with your FND_ORACLE_USERID table to make them eligible for statistics gathering. This could be a potential solution to insure that statistics are being gathered for all of the schemas in your database. It is important to mention, however, that statistics should NOT be gathered for SYS or SYSTEM objects on Version 8i or earlier databases (hopefully everyone is off of those versions). The Version 9i database saw neutral recommendations regarding SYS and SYSTEM in that Oracle said you could run statistics for these objects but that you were not required to. Of course, anyone doing upgrades with the E-Business Suite and the Version 9i database saw instructions on gathering statistics for better performance. But, you also probably noticed that there were still several procedures run by Oracle that were “rule” based and therefore could be negatively impacted by statistics.

All of this changed with Oracle 10g, though. This later version of the database, which is certified for higher versions of Release 11i, requires statistics to be gathered on the SYS and SYSTEM objects. Simply put, the “rule” base optimizer is out in 10g and the “cost” based optimizer is in. Unfortunately, Oracle did not update the FND_STATS package for this occurrence and actually wrote in code to prevent the gathering of statistics on SYS objects. No doubt this was a hold over to prevent statistics gathering on the earlier versions of the database. We hope that Oracle will remove this in Release 12, as Release 12 is only certified on Oracle 10g and above. For now, however, you will need to use DBMS_STATS from the command line to gather statistics on SYS objects. For this, we recommend using a script that is run out of the crontab on a periodic basis.

In the end, we have shown you how to use the concurrent program Gather Schema Statistics to run statistics for every schema in your database with the exception of the SYS schema. Unless you have custom scripts already meeting all of your statistics gathering needs, we recommend exploring the preceding option. Doing so would make it simple to insure that all schemas are picked up, that histograms can be correctly identified for all objects, and that management for gathering statistics can be handled in this one well known interface.

Finally, as promised earlier, there are many experts on statistics in our community, but I find that I gravitate towards the following (in no special order):

• Jonathan Lewis => Mr. Lewis wrote the book on it, literally. We consider him to be an invaluable resource for the Oracle community and very much appreciate his sharing his knowledge with us. His website is http://www.jlcomp.demon.co.uk/ while he can be found on his blog at http://jonathanlewis.wordpress.com/ Jonathan will be teaching classes (Troubleshooting and Tuning , Writing Optimal SQL and Explain Plan) in Salt Lake City and Minneapolis from September 8-12 and is a terrific instructor.

• Wolfgang Breitling => Mr. Breitling has written numerous articles on statistics gathering and frequently presents at some of the industries best conferences. His website is http://www.centrexcc.com/

• Christian Antognini => Mr. Antognini is a recognized expert in the field of performance tuning and has written several articles / presentations relative to statistics and their impact on performance. His personal website is http://antognini.ch/ while his blog is at http://antognini.ch/blog/

• Oracle’s Optimizer Team => The team’s blog is at http://optimizermagic.blogspot.com/feeds/posts/default?alt=rss

We hope you enjoyed the entry and hopefully some of you learned something new. As always, we are interested in your feedback or ideas you have for other articles. Please fell free to let us know…

If you enjoyed this post, make sure you subscribe to my RSS feed!