DOUG presentation: Why does optimizer hate my SQL?

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

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

[…] 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 . […]

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.

* 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.

* 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.

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.

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 🙂