Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Gathering CBO Statistics in Oracle Applications

with 12 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

12 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

  3. Hi Carlos,

    we are currently using Siebel 8.1.1.11 in development and test, and upgraded to database 12.1.0.2 (Windows x64). I noticed that Siebel performance is very bad now. I tried collecting statistics using default parameters for dbms_stats package (and collecting stats for ALL tables) and it increased performance a lot.
    When reverting back to coe_siebel_stats, the application is nearly unusable. It seems like the 12c optimizer does have some serious issues with the statistics collection of this script in our environment.

    question 1: Is there an updated script for 12c available? Couldn’t find anything on MyOracleSupport.
    question 2: How would your statistics script affect the usage of the In-Memory option of 12c, when we wanted to use it with Siebel?

    Thanks Carlos, your help is appreciated!

    Ben

    Ben

    December 9, 2014 at 11:59 am

    • Ben,
      If collecting stats for Siebel on 12c using out of the box APIs gives you better performance, then keep using that method.
      1. There is no 12c version of coe_siebel_stats. Keep in mind this script was needed on 10g and 11g to correct some deficiencies of regular DBMS_STATS APIs.
      2. Gathering stats with a script or using standard DBMS_STATS APIs has no direct relationship with in-memory database. Stats are needed for the CBO to generate optimal plans.

      Carlos Sierra

      December 9, 2014 at 2:35 pm

      • Hi Carlos,
        thak you for the quick reply!
        We’ll go with the default settings then.
        Ben

        Ben

        December 10, 2014 at 2:34 am

  4. Carlos,

    I’d be glad if you could answer some of my questions related to the coe_siebel_stats.sql.
    I understand the role of histograms in general but I’d like to understand it more in context of Siebel which is somehow “special” kind of OLTP DB.
    In the most recent version (10g, 11g) of the script I see following:
    IF i.table_name IN (‘S_POSTN_CON’, ‘S_ORG_BU’, ‘S_ORG_GROUP’) THEN
    method_opt := ‘FOR ALL COLUMNS SIZE 254′;
    ELSE
    method_opt := ‘FOR ALL INDEXED COLUMNS SIZE 254′;
    END IF;
    My understanding is that due to above ELSE clause, for all tables that will have statistics gathered we will generate histograms with max number of 254 buckets for ALL indexed columns.
    My understanding is also that Siebel favors index access paths for all queries (optimizer_index_cost_adj = 1), essentially allows “nestet loops” as the only join mechanism and instructs CBO for the best response time:

    alter session set optimizer_mode = first_rows_10;
    alter session set “_hash_join_enabled” = false;
    alter session set “_optimizer_sortmerge_join_enabled” = false;
    alter session set “_optimizer_join_sel_sanity_check” = true;

    Also, Siebel uses bind variables for predicates in most of the cursors. Also, (what I’ve learned from you) it will be very rare and rather not desirable if in newer Oracle DBs the “adaptive cursor sharing” would kick in. We would be never (almost) interested in Siebel in having alternate execution plan generated, that would prefer full table access over index access.

    So in short, in Siebel we want the first query results being returned as quickly as it gets and that can usually be guaranteed by index access and nested loops joining strategy.

    Provided the above understanding of mine is correct, my questions are:
    – what is the preliminary reason for having histograms in Siebel – is it ONLY about join cardinality and resulting join order or are there OTHER reasons?
    – why the method_opt := ‘FOR ALL INDEXED COLUMNS SIZE 254′ option which creates not only frequency but also high balanced histograms (also for columns with unique constraints like ROW_ID)
    – does the Oracle DB version matter i.e. would the above recommendation change for Siebel on 11.2 or 12.1?

    Thank you
    Michal

    Michal

    December 17, 2014 at 7:52 am

    • Oops, sorry for the typos – it should read “nested loops” and “height balanced” in the paragraphs above

      Michal

      Michal

      December 17, 2014 at 8:03 am

    • Michal,

      Your’s are very good questions:

      1. Why do we need histograms on Siebel?

      Histograms allow the CBO to be more accurate in its determination of Cardinality and Selectivity. If there are multiple filter predicates on your SQL, these histograms help to decide leading Table and access method (important if there are multiple indexes that could be used). The assumption here for Siebel is that subsequent values of bind variables would render similar Selectivities. And if that is the case, then the CBO would had generated a “better” plan not only for 1st execution but also for subsequent executions.

      2. Why method_opt “for all indexed columns size 254″?

      This parameter assumes that all columns referenced by a predicate have a single column index on it (which is a Siebel rule). Generating a histogram on a column with unique values is a waste, unfortunately DBMS_STATS does not discriminate, so we end up having height balanced histograms on columns for which the histogram does not help (a simply waste).

      3. Would the above recommendation change for 11R2 or 12c?

      Yes and no. As far as I know, there are no plans to revisit this gathering of stats for Siebel any time soon. Actually, when we evaluated the impact of using default values of DBMS_STATS APIs versus legacy values like the ones you indicated, we encountered no significant difference (we did this on 11g), but Siebel Development decided to keep using the same set of non-default parameters just for the sake of consistency with prior versions (i.e. 10g and even 9i). So, I would expect this recommendation to use non-default parameters on DBMS_STATS for 11g and 12c to fade away, and start using default DBMS_STATS calls. This is not my call, but Siebel Development call.

      Cheers — Carlos

      Carlos Sierra

      December 17, 2014 at 1:43 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,689 other followers

%d bloggers like this: