Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Why do you need to gather CBO Statistics?

with 4 comments

As I help a peer with a SQL Tuning engagement, I face the frequent case of: “We do not want to gather CBO schema object statistics because we don’t want our Execution Plans to change”. Well, the bad news is that: not gathering stats only gives you a false sense of safety because your Execution Plans can change anyways. The reason has to do with Predicates referencing values out of range. Typical cases include range of dates, or columns seeded with values out of a sequence (surrogate keys). Most applications use them both. Example: predicate that references last X days of data. Imagine that date column on this predicate actually has statistics with low and high value that are outdated, lets say the high value refers to last time we gather stats (several months old). In such cases, the CBO uses some heuristics starting on 10g, where the cardinality of the Predicate is computed according to range of low/high and how far the value on Predicate is from this low/high range as per the stats. In short, the cardinality changes over time, as the Predicate on the last X days of data changes from one day to the next, and the next, and so on. At some point, the CBO may decide for a different Plan (with lower cost) and the performance of such SQL may indeed change abruptly. Then we scratch our heads and repeat to ourselves: but we did not gather statistics, why did the plan change?

So, if you understand the rationale above, then you would agree with the fact that: not updating CBO schema stats do not offer any real Plan Stability. So, my recommendation is simple: have reasonable CBO statistics and live with the possibility that some Plans will change (they would change anyways, even if you do not gather stats). Keep always in mind this:

The CBO has better chances to produce optimal Plans if we provide reasonable CBO statistics.

Now the good news: if you have some business critical SQL statements and you want them to have stable Plans, then Oracle already provides SQL Plan Management, which is designed exactly for Plan Stability. So, instead of gambling everyday, hoping for your Plans not to change preserving outdated stats, rather face reality, then gather stats, and create SQL Plan Baselines in those few SQL statements that may prove to have an otherwise unstable Plan and are indeed critical for your business. On 10g you can use SQL Profiles instead.

On 10g and 11g, just let the automatic job that gathers CBO schema statistics do its part. In most cases, that is good enough. If you have transient data, for example ETL tasks, then you may want to have the process workflow to gather stats on particular Tables as soon as the data is loaded or transformed and before it is consumed. The trick is simple: “have the stats represent the data at all times”. At the same time, there is no need to over do the stats, just care when the change on the data is sensible.

Advertisements

Written by Carlos Sierra

November 11, 2014 at 11:58 am

4 Responses

Subscribe to comments with RSS.

  1. Hello Carlos,

    I have 2 questions regarding this.
    1. Is there a specific method to collect stats for E-Business Suite R12 (12.1.3)?
    2. Does this change significantly for 12c DB with E-Business Suite R12 (12.1.3)?

    Thanks,
    Dhilip Ranganathan.

    Dhilip Ranganathan

    November 11, 2014 at 12:42 pm

  2. I have seen this behavior in many a shop, and it’s not always because of ignorance. In my theory, it’s due to lack of power or control on the part of the DBA team. They are told not to update stats by development, when, in fact, they should be insisting on the collection of timely stats each and every time data changes within the application. Prime examples are batch processing which load or delete many rows. It’s very difficult to do, but the DBAs ought to have the power to insist this be included every time a major change is made.

    Michael Fontana

    November 11, 2014 at 3:41 pm

    • indeed. quite common development does not know better, so they stick to the myth “if i do not change stats my plan does not change”. so dbas have to convince developers…

      Carlos Sierra

      November 11, 2014 at 5:02 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

%d bloggers like this: