Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

DOUG presentation: Why does optimizer hate my SQL?

Posted by Riyaj Shamsudeen on April 18, 2009

I presented about Cost based optimizer explaining why some times CBO chose inefficient access plan, even though, there is an efficient plan in the search space. This entry is to post presentation slides and they can be downloaded from Why_optimizer_hates_my_sql

.

Update: Updated presentation after Greg’s comments.
Update2: Further bug fixes in presentation and script.
Update3: Updates after Randolf’s comments.

8 Responses to “DOUG presentation: Why does optimizer hate my SQL?”

  1. [...] Oracle database internals by Riyaj added an interesting post today on DOUG presentation: Why does optimizer hate my SQL?Here’s a small readingI presented about Cost based optimizer explaining why some times CBO chose inefficient access plan, even though, there is an efficient plan in the search space. This entry is to post presentation slides and they can be downloaded from Why_does_optimizer_hate_my_sql . [...]

  2. Greg Rahn said

    Can you explain this recommendation from slide 15?

    If there are few distinct values then use that many # of buckets.
    Else use 100 or 250 depending upon the size of the table.

    What is special about 100 or 250 buckets?
    Why not use size auto?

    • Hi Greg
      Thanks for reading my blog. You are right about that slide and there is nothing special about 100 or 250. It is just convenience for me to visualize the histograms using one of my scripts with preset values. But, I realize that it shouldn’t be a generic recommendation. I have updated the presentation correcting it.

      Cheers
      Riyaj

  3. Riyaj,

    thanks for sharing the presentation. Some comments:

    * Slide 25: Dynamic sampling is already available in 9i, so it’s not a 10g feature. It’s only that 10g by default uses level 2 whereas 9i defaults to level 1.

    * Slide 27: You don’t need histograms to get different plans through bind variable peeking. Range comparisons (greater than / less than etc.) or LIKE with binds are also subject to plan changes with bind variable peeking

    * Slide 50/51: I think on slide 50/51 you’ve mixed the “good” / “bad” clustering factor. You say on slide 50 “high clustering factor” but the graphics suggest that it’s a “good” clustering factor. But a “good” clustering factor is “low” since it’s close to the number of blocks. On slide 51 you say “low” clustering factor but again the graphics suggests a “bad” clustering factor which is “high” i.e. close to the number of rows of the table.

    * Slide 55: You can index null values. You only need to add to the index a non-null column or expression. Only if the whole expression of the index is null it won’t be indexed.

    Your example:

    select * from t1 where n1 is null;

    create index t1_idx on t1 (n1, 0);

    or

    create index t1_idx on t1 (n1, ”);

    select * from t1 where n1 is null;

    —————————————————————————
    | Id | Operation | Name | Rows | Bytes | Cost |
    —————————————————————————
    | 0 | SELECT STATEMENT | | 100 | 20400 | 10 |
    | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 100 | 20400 | 10 |
    |* 2 | INDEX RANGE SCAN | T1_IDX | 100 | | 2 |
    —————————————————————————

    Predicate Information (identified by operation id):
    —————————————————

    2 – access(“T1″.”ID” IS NULL)

    * Slide 68: “first_rows_N for OLTP”: I don’t second that. Usually a typical OLTP query identifies a very selective subset of the data, and therefore fetches all the rows identified, therefore ALL_ROWS should be in most cases the correct mode.

    See e.g. http://jonathanlewis.wordpress.com/2008/11/11/first_rows_n/

    FIRST_ROWS_N is usually only applicable if you actually fetch only a part of the identified rows which are typically “pagination” or “top n” style queries. Since these should make correct use of the ROWNUM predicate the optimizer by default switches to FIRST_ROWS_N optimization (controlled by the internal parameter “_optimizer_rownum_pred_based_fkr”). Only if you e.g. use analytic functions for the “top n” or “pagination” an explicit activation of the FIRST_ROWS_N mode should be required.

    Regards,
    Randolf

    Oracle related stuff blog:

    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):

    http://www.sqltools-plusplus.org:7676/

    http://sourceforge.net/projects/sqlt-pp/

    • Hello Randolf
      I just found your comment in the spam folder. No idea why it went in to spam and that’s why the delay is.
      1. Of course, I knew that :-) I guess, your point is that second line should say, prior to 11g, we can mitigate this with dynamic sampling at level 4. I agree and will correct it.
      2. True and I said that during my presentation. That line in 27 was explaining the example in later slides. I will modify that little bit to avoid confusion.
      3. 50/51 slides. Yes, I caught that during the presentation itself. But, forgot to recreate pdf before uploading.
      4. This is exactly why I prefer to have presentation and a paper. I explained that during presentation too.
      5. Okay your point well taken. I still think that much of OLTP queries perform better with first_rows_N optimizer mode (and I have used that successfully). But, I can see how that can also have negative effect especially after reading Jonathan’s note. Let me see if i can word it better.

      Thanks for the feedback Randolf. I will be adding your site as reference in this presentation :-)

      Cheers
      Riyaj

  4. fredericktang said

    Nice presentation, I was able to pick off quite a few tips.
    Thanks.

    rgds,
    fred

  5. Kumar said

    Hi Riyaz
    Very good presentation in easy to understand format. The nice thing is it has examples which can be tried and confirmed.

    - kumar

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 214 other followers

%d bloggers like this: