Oracle Scratchpad

February 9, 2011

Philosophy – 13

Filed under: Philosophy — Jonathan Lewis @ 6:32 pm GMT Feb 9,2011

If you see a comment like “X is a bad idea” this does not mean “some mechanism that is vaguely ‘not X’ is a good idea”.

If, for example, I say:

    “Histograms will not work well on character strings that are more than 32 bytes long and generally similar in the first 32 bytes”

that is absolutely not the same as saying

    “It’s a good idea to create histograms on character strings that are less than 32 bytes long.”

If this were a purely mathematical world we could invoke symbolic logic and point out:

(A => B) <=> (¬B => ¬A)

which means my statement is equivalent to:

    if you have a histogram that is working well then the data is not character strings of more than 32 bytes with generally similar values in the first 32 bytes”

Of course, being Oracle, you may find that someone, somewhere, has exactly such a histogram that appears to work brilliantly for them – but that will be because the optimizer has messed up the arithmetic so much that they are getting a great execution plan for completely the wrong reason … so they need to watch out for the next upgrade or patch release in case the optimizer gets enhanced.

[The Philsophy Series]

5 Comments »

  1. Brilliant!
    :-)

    Comment by Toon Koppelaars — February 9, 2011 @ 7:34 pm GMT Feb 9,2011 | Reply

  2. I’ve always found it tricky to explain to a victim that actually the plan that used to work so well was picked for the wrong reasons and it’s now picking a much worse plan but for the right reasons.
    Often that’s because you’ve had to do a fix to improve things elsewhere.

    Comment by Gary — February 9, 2011 @ 8:39 pm GMT Feb 9,2011 | Reply

    • I’ve always found it tricky to explain to a victim that actually the plan that used to work so well was picked for the wrong reasons and it’s now picking a much worse plan but for the right reasons.

      A recent example for this: a query which run perfectly well in 9iR2 started to run very slow in 11gR2. It is a simple join with couple of filter conditions on both tables. Due to the fact that CBO in 9i had no idea that data in two columns is correlated, it considered two plans, both low cost but picked the first it considered. CBO was blind in 9i and cardinality estimates were way off for both tables, but it was the right plan. In 11g, however, it was able to calculate selectivity for a condition on two columns of the table based on an index’s distinct_keys. That implied change in the order of plans. Both are the same low cost, but the first is totally wrong, because, well, there are several conditions (including like ‘X%’) and all of them are not selective at all, which is enough to screw things up.

      Comment by Timur Akhmadeev — February 10, 2011 @ 8:54 am GMT Feb 10,2011 | Reply

  3. that reminds me of a discussion I had with my wife a few days ago:
    She said, when she drinks more than 32 cups of coffee a day, she will not be able to sleep at night.
    For me, the opposite is true: when I sleep at night, I am not able to drink any coffee.
    Funny, isn’t it ?

    Comment by Sokrates — February 10, 2011 @ 9:23 am GMT Feb 10,2011 | Reply

  4. Let me just add to the phenomenon “Optimizer of new version picks worse plan for the right reason” that we introduced SQL Plan Baselines in 11g to prevent this – similar as Stored Outlines could in previous versions but with the chance that better plans can be evolved and used.

    Comment by Uwe Hesse — February 11, 2011 @ 10:36 am GMT Feb 11,2011 | Reply


RSS feed for comments on this post. TrackBack URI

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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,429 other followers