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.
Oracle database internals by Riyaj 
Topics about Douglas-schools » DOUG presentation: Why does optimizer hate my SQL? said
[…] 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 . […]
Greg Rahn said
Can you explain this recommendation from slide 15?
What is special about 100 or 250 buckets?
Why not use size auto?
orainternals said
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
Randolf Geist said
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/
orainternals said
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
fredericktang said
Nice presentation, I was able to pick off quite a few tips.
Thanks.
rgds,
fred
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
orainternals said
Hi Kumar
Thank you for visiting my blog and Thank you..