A few days ago, I recorded a Web presentation at Oracle’s offices in Reading. The topic I chose was the one that I ‘ve just repeated at the Hotsos 2007 event: dissecting one query to see how much you can learn about how the optimizer works, and how you have to think when dealing with a difficult problem in SQL.
The entire presentation – one hour long, audio synchronised with slide-show as a Macromedia Breeze presentation – can be heard at this rather long URL
The generic structure of the query used in the presentation was the one I first introduced in the blog item about Join Orders.
Oracle Scratchpad 
Can we download it?
It’s very interesting!
and…well…nice voice! ;)
Comment by Antonio — March 6, 2007 @ 7:51 am GMT Mar 6,2007 |
on slide 24 you showed the work Oracle 9 and Oracle 10 does when looking for join order.
You noticed that Oracle 10 can be a bit CPU consuming: can we reduce (or “control”) this overhead by “tuning” the optimizer_max_permutations init parameter?
Comment by Antonio — March 6, 2007 @ 10:02 am GMT Mar 6,2007 |
Antonio, I don’t know the technology used to put the presentation online, so I don’t know if it can be downloaded – it’s completely outside my control.
The parameter “optimizer_max_permutations” applies separately to each query block that has to be optimized, so wouldn’t have the effect that you are thinking of. You can, however, disable cost-based query transformation by setting the undocumented parameter “_optimizer_cost_based_transformation” to the value “off”. This can be done at the system or session level, or at the statement level using the (also undocumented) opt_param hint.
As ever, using undocumented features means “at own risk” and “ask Oracle support to agree first”.
Comment by Jonathan Lewis — March 6, 2007 @ 10:21 am GMT Mar 6,2007 |
Clear as always.
Thank you!
Comment by Antonio — March 6, 2007 @ 1:05 pm GMT Mar 6,2007 |
Superb.
And nice to put a voice to the photo.
Comment by dombrooks — March 6, 2007 @ 2:15 pm GMT Mar 6,2007 |
Jonathan, both the layout …and content (of course) are pretty awesome! I only hope the-powers-that-be appreciate it ($$$) accordingly ;-)
the “downloadable” thought, sorry to say it, will not be put to rest, until it is achieved :-) LOL too valuable of a resource to be ignored. So, the podcasting ante has been upped ;-)
Comment by cos — March 6, 2007 @ 3:24 pm GMT Mar 6,2007 |
On the “Subquery With OR” slide, you replace the “OR” with a “UNION ALL”. Then additional predicates are used in order to eliminate duplicates.
Why don’t you simply use “UNION” rather than “UNION ALL”, and let Oracle eliminate any duplicates for you?
Comment by Milo van der Leij — March 6, 2007 @ 3:59 pm GMT Mar 6,2007 |
Milo van der Leij,
there is at least two catches with union.
First – small_vc column need not be unique. So by union you can eliminate right data.
Second – union implies a “sort unique” operation of a resultset. So even if small_vc is unique I would expect it be more efficient to get rid of duplicates through filter rather than whole resultset sorting.
I believe #1 was Jonathan’s reason.
Jonathan,
excellent presentation!
Comment by Alexander Fatkulin — March 7, 2007 @ 1:33 am GMT Mar 7,2007 |
Milo, Alexander’s comments are correct. The query is not logically equivalent unless it is written as a union all with a filter.
Imagine I had two rows (n1, n2, small_vc) with the values (1, 100, ‘xxx’) and (2, 999, ‘xxx’).
The first row would be selected in the first part of the union all, and the second could be select in the second part. But in both cases, all I get in the select list is the value ‘xxx’.
I expect to see two identical rows in the output – but switching to the union would eliminate duplicates.
Comment by Jonathan Lewis — March 7, 2007 @ 3:12 am GMT Mar 7,2007 |
Thanks Alexander, Jonathan. I somehow read over that part and assumed it was selecting * from t1.
I thought Alexander’s second explanation might have been the reason, which is what prompted me to ask the question. (Hoping it would lead to some discussion about UNION’s inefficiency and whether it should be avoided when possible. But, alas. :))
Comment by Milo van der Leij — March 7, 2007 @ 2:45 pm GMT Mar 7,2007 |
If you can pull the individual files through, it should be possible to build a ‘local copy’.
The individual files can be dug out of the following XML…
http://oukc.oracle.com/static05/opn/oracle9i_database/022607_1000/022607_1000_source/022607_1000/data/viewer.xml
Basically SlideNN.swf and a24xNN.mp3 (with NN being 1 to 42).
Geek prize to the first to write the SQL to extract the filenames plus the PL/SQL to UTL_HTTP the whole thing into BLOBs
Comment by Gary — March 8, 2007 @ 11:54 am GMT Mar 8,2007 |
Jonathan – On the “Impossible Plan” slide, you said Oracle will not do bushy tree plans on it’s own, you need to help it or something similar. By helping, did you mean you have to hint it? Or is coding the query with in-line views often sufficient? After listening to the presentation I was caught off guard when I checked this query and found a bushy tree plan with no hints. Am I just lucky or what?
{query and plan removed because of problems of readability and sent by email}
Comment by Steve — March 8, 2007 @ 9:43 pm GMT Mar 8,2007 |
Steve, I have deleted the query and the execution plan as they were virtually unreadable. However, having seen the query and plan that you emailed to me, it’s an example where you had a couple of non-mergeable in-line views anyway.
In my example the “simple” attempt to break the query into two parts produced two mergeable views, which Oracle would have merge back to reproduce the four-table join.
That’s why this strategy has to use the /*+ no_merge */ hint on the parts to make sure that Oracle optimises the pieces separately.
Comment by Jonathan Lewis — March 9, 2007 @ 1:29 am GMT Mar 9,2007 |
Mirror done! :)
It’s a 22Mb rar…ehm…how can I share it?
Or better: Can I share it?
Is it legal?
Comment by Antonio — March 9, 2007 @ 8:22 am GMT Mar 9,2007 |
Antonio, no, it wouldn’t be legal to share it if by “sharing” you mean publish it on the internet.
It has my copyright notice on every page and Oracle paid for the privilege of publishing the content. Oracle also invested their time and efforts in the development and editing of the delivery infrastructure, so have an interest in the copyright of the final product as well.
I have no problems with people making personal copies of the material that I put on the internet – for many people it’s a very sensible option – but creating “mirrors” is another matter.
Comment by Jonathan Lewis — March 9, 2007 @ 1:22 pm GMT Mar 9,2007 |
@Jonathan
I made only a personal copy following more or less what Gary suggested.
For mirror I meant a download for personal use not for publication.
If someone are interested I must say that what Gary said is correct but not complete.
That’s all.
P.S.
Jonathan if you think that it’s better to edit or delete this comment…well you’re welcome! :)
Comment by Antonio — March 9, 2007 @ 2:59 pm GMT Mar 9,2007 |
Hi Jonathan,
excellent presentation. Thank you for sharing this. It’s amazing what there is to learn about a ‘simple’ join.
Comment by Peter — March 9, 2007 @ 3:58 pm GMT Mar 9,2007 |
[…] his Oracle Scratchpad, Jonathan Lewis also has a very nifty multimedia presentation of his Hotsos material available, this one on, “…dissecting one query to see how much you can learn about how […]
Pingback by Pythian Group Blog » Log Buffer #35: a Carnival of the Vanities for DBAs — March 9, 2007 @ 8:17 pm GMT Mar 9,2007 |
Why doesn’t the USE_CONCAT hint do what you did manually on slides 11 and 12 (OR vs. UNION ALL)?
Comment by Jason Bucata — March 9, 2007 @ 9:46 pm GMT Mar 9,2007 |
Again with slide 12: If you haven’t done so before, it would be terrific to see a NULL tutorial from you. I understand how they work but I’ve seen enough people get tripped up by them…
Comment by Jason Bucata — March 9, 2007 @ 9:50 pm GMT Mar 9,2007 |
Jason, “why doesn’t use_concat …”: it just doesn’t (at present). Maybe one day it will.
As far as NULLs go, I don’t think I could do anything better than this note,written a couple of years ago by Lex de Haan and Jonathan Gennick.
Comment by Jonathan Lewis — March 10, 2007 @ 2:06 am GMT Mar 10,2007 |
I have problem in opening demo page (slide 13). Is there any direct link to demo ?
Comment by Rudi — March 11, 2007 @ 9:24 am GMT Mar 11,2007 |
Rudi, I’ve just tried running it. When you get the pop-up window that is supposed to show the demo, but fails to find it, the URL needs to be changed so that “Demo.html” becomes “demo.html”.
Corrected URL for demo
I’ve sent an email to the person at Oracle who set up the Webcast, so this may change at some future point.
Comment by Jonathan Lewis — March 11, 2007 @ 12:48 pm GMT Mar 11,2007 |
simply great !
just one cosmetic thing, on slide 48 there is a minor type while giving the hint “materialize”
Comment by Vikas Atrey — June 2, 2007 @ 6:28 pm BST Jun 2,2007 |
Vikas, thanks for pointing that out. Unfortunately I can’t update the image as it’s outside my control.
Comment by Jonathan Lewis — June 3, 2007 @ 9:25 am BST Jun 3,2007 |