If you have to deal with hierarchical queries (or “connect by” queries, as they’re commonly known) you might be interested in this note on the OTN Forum where someone has a problem with repeated executions of the same query (same inputs, same results, same execution plan) operating at wildly different speeds with amazing changes in workload.
Ultimately the solution was the same as for the more obvious question: “why does my sort sometimes take much longer than usual?” – shortage of memory (in this case a very low setting for the pga_aggregate_target).
This example is an interesting variation, though, as it demonstrates how the availability of resources can make Oracle choose to execute different parts of an execution plan – giving another reason for the “conditional plan” strategy I’ve described in the past.
There’s an interesting post on hierarchical queries on Christian Antognini’s blog (which I’ve also referenced from the OTN posting)
Oracle Scratchpad 
On a slightly different note, I’ve always been mystified, although not too much, by apparent Oracle unwillingness to implement the standard Sql’99 recursive query (recursive WITH … UNION ALL). After all, in addition to DB2 and SQL Server, even Postgress and Teradata have the recursive WITH.
On the face of it, both ways to recurs seem to be equally powerful, but the latter has the advantage of being standard.
Comment by Val — July 6, 2009 @ 2:58 pm BST Jul 6,2009 |
Recursive WITH is expected in Oracle 11gR2.
Comment by Timur Akhmadeev — July 9, 2009 @ 11:23 am BST Jul 9,2009 |
Timur,
Thanks for that bit of news.
Comment by Jonathan Lewis — July 9, 2009 @ 6:41 pm BST Jul 9,2009 |
Hi Jonathan,
Thanks for answering my post on OTN.
On my vacation in Switzerland I read ‘My family and other animals’ written by Gerald Durrell.
One part reminded me of you when he wrote that his new teacher at Korfu must have been certainly a good teacher since he had a beard.
You didn’t shave off yours, did you?
regards Hans-Peter
Comment by Hans-Peter — August 11, 2009 @ 6:09 am BST Aug 11,2009 |
Hans-Peter,
Gerald Durrell was one of my favourite authors in my youth – although I haven’t read any of his books recently.
I have always felt that to be a truly excellent Oracle specialist you have to have a “full-set” (English Navy term for beard and moustache) – which is probably why Tom Kyte finally grew a set a couple of years ago.
Comment by Jonathan Lewis — August 11, 2009 @ 8:29 pm BST Aug 11,2009 |