Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Gathering CBO Statistics in Oracle Applications

with 5 comments

When we refer to Oracle Applications we usually think E-Business (EBS), but there are other large Oracle Applications that requires some special consideration when it comes to CBO Statistics gathering: Siebel and People Soft (PSFT). There are some others, but I would say EBS, Siebel and PSFT make the bulk of them all.

Gathering Stats on EBS

This is easy, since the only supported method is FND_STATS. That means using plain DBMS_STATS and ANALYZE is simply not supported. FND_STATS is a wrapper on top of DBMS_STATS that includes some extra functionality. The most important addition is controlling which columns get Histograms. The set of columns EBS requires to have Histograms is small and well controlled. FND_STATS can be executed from within the application (Concurrent Request, i.e. batch job) or directly from SQL*Plus.

If you are using EBS, then be sure FND_STATS is the only active method to gather statistics. In other words, automatic jobs to gather statistics using DBMS_STATS or custom scripts must be disabled.

Gathering Stats on Siebel

Siebel documents how CBO statistics must be gathered in white paper titled “Performance Tuning Guidelines for Siebel CRM Applications on Oracle Database“. This paper is authored by “James Qiu, Paul Blokhin, Mehdi Gerami” (Oracle Development) and its latest version is dated December, 2010. It was rewritten back on 2010 with the contribution of Abel Macias, Dave Henriquez and myself. Out of this paper we created the coe_siebel_stats.sql and the coe_siebel_profile.sql scripts. Both are well documented. Please refer to Metalink (MOS) note 781927.1 to download the latest version of the white paper and the actual scripts.

If you are using Siebel on databases 10.2 or higher, you really want to follow the white paper provided in MOS 781927.1 and implement coe_siebel_stats.sql. We have noticed that since its inception a couple of years ago, the number of performance issues reported in Siebel applications has been reduced significantly.

Gathering Stats on PSFT

For PeopleTools 8.49 and higher, on databases 10.2.0.4 and higher, I recommend the use of Metalink (MOS) note 1322888.1 (Improving Statistics in Oracle RDBMS for PeopleSoft Enterprise). The author of this note is John Houghton, who works for the Center of Expertise (CoE) in the PSFT side of the house. In this MOS note you will find a zip file that contains a couple of documents explaining how to use script pscbo_stats.sql and it also contains the actual script. There is some implementation effort, but once pscbo_stats.sql is implemented your CBO statistics are gathered following best practices for PSFT. I had the opportunity to collaborate with John in the creation and testing of this pscbo_stats.sql script. It is currently on its 2nd major release.

If you are on PSFT 8.49 or higher, you want to implement pscbo_stats.sql out of MOS 1322888.1. Feedback on the results of using this tool have been positive in most cases.

Conclusion

The Cost-based Optimizer (CBO) requires to input good statistics in order to produce an optimal plan. In the perfect world we would use DBMS_STATS with no parameters. Still, some specific application design characteristics, like very skewed data or the use of small tables and temporary tables, require a specif setup to gather statistics tailored to the needs of these applications. All FND_STATS, coe_siebel_stats.sql and pscbo_stats.sql are wrappers on top of DBMS_STATS that incorporate fine granularity use of DBMS_STATS parameters to gather statistics better suited to their application. For EBS the use of FND_STATS is a must, for the other two the use of their corresponding scripts are not a must but strongly recommended.

Written by Carlos Sierra

July 25, 2013 at 7:23 am

5 Responses

Subscribe to comments with RSS.

  1. Hello Carlos,

    We’ve recently upgraded to Siebel 8.1.1.11 running on 11.2.0.4. We are also running the coe_siebel_stats.sql on a regular basis. My question is regarding the collection of system and dictionary stats.

    On the previous version of Oracle (10gR2) we never collected any system level stats. I would periodically see evidence of bad system type stats such as slow response against some v$… views, lots of temp space being chewed up during queries, direct temp access, etc. If I collected system-type stats in a test environment the performance issues would go away. I have been strongly cautioned against collecting any type of system stats but this seems counter to good practice and in our 10g environment, it appeared warranted but never done for fear of disrupting production.

    What is your recommendation on system level stats and Siebel running on 11gR2? Is there an easy answer to this question or is it more a matter of waiting to see how performance plays out and make adjustments?

    Thank you.

    CT.

    August 25, 2014 at 2:34 pm

    • CT,

      There are 4 types of CBO Statistics:
      1. Schema Object
      2. Data Dictionary
      3. Fixed Objects
      4. System Stats

      From the 4, coe_siebel_stats takes care of #1. The others, are like any other database.
      For Data Dictionary, you can set the DBMS_STATS AUTO Job to collect Data Dictionary stats, by using SET_GLOBAL_STATS for AUTOSTATS_TARGET = ORACLE.
      For Fixed Objects, which affect the performance of queries on top of V$ views, you want to use the API DBMS_STATS provides, just be careful when you do this, since it may cause some transient locking.
      For System Statistics, you may want to simple use DELETE_SYSTEM_STATS and not gather them.

      Carlos Sierra

      August 25, 2014 at 2:53 pm

  2. Hello Carlos,

    If a Siebel customer is currently using the automatic database statistics collection job , without following the recommendations in Note 781927.1, and now he wants to implement the recommended procedure by scheduling the coe_siebel_stats.sql script, then:
    1- Should he start by deleting all existing statistics?
    2- Can the existing statistics be used as base for the statistics collected by the coe_siebel_stats.sql script?
    3- Or should he delete the current statistics, then run the coe_siebel_stats.sql in ‘b’ mode then in ‘N’ mode?.

    Regards
    Ahmed

    Ahmed Abdel Fattah

    September 25, 2014 at 4:02 am

    • Ahmed,
      There is no need to delete the existing stats. Actually, you don’t want to do that. Execute it one with B (baseline) then use N (normal) execution afterwards.
      Cheers

      Carlos Sierra

      September 25, 2014 at 1:38 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,676 other followers

%d bloggers like this: