Tanel >> whether (or showing that ;) my systematic approach actually works! You must be kidding :-) Of course, systematic approach works. I encountered a bug while writing a paper on CBQT (well, what do you know, for hotsos symposium) in which Parsing was taking long time. I dumped pstack in a loop to see where the time is spent and I realized that it was in kk* module. Of course, I reduced complexity to make my test case simpler and reduce parse time (and then I rewrote test case with emp and dept table examples to improve readability). So, yes, it is a byproduct of scientific approach, only I can't find that test case anymore :-( Basically, multiple exists and OR operators nested within, explodes Cost based query transformation options leading to higher parse time, IIRC. Cheers Riyaj Shamsudeen Principal DBA, Ora!nternals - http://www.orainternals.com Specialists in Performance, Recovery and EBS11i Blog: http://orainternals.wordpress.com On Thu, May 7, 2009 at 9:46 AM, Tanel Poder <tanel@xxxxxxxxxx> wrote: > Hi Ric, > > Yeah, there's always the reality factor. Sometimes even if there would be a > great chance to diagnose the root cause of a hang, some director decides > "just reboot the damn database", which can be the right decision from > business perspective (but has the risk of becoming accepted reality & > standard practice). > > And yep, as 10053 trace doesn't have timing info in it, it's impossible to > directly conclude anything about breakdown of parse TIME from there. That's > why proper instrumentation is important. In 10053 tracefile case I would > take a step back and look into number of join orders evaluated. Again you > can't directly map this to time, but if you see 1 million join orders > evaluated for a 8-table join, there's an immediate problem(bug) evident > from > there. And then you could look which orders were re-evaluated the most to > get some clue about where this issue happens. But if you don't see > ridiculously high number of join orders evaluated, it means that either > some > or all join order evaluations take long time. And this is where stack > profiling comes into play as it would give you an idea which operations > took > most of TIME - as the longer a function execution takes, the more stack > samples contain that function's name! > > Btw I didn't intend to bash the non-systematic approach here, there is > always the case of looking into the usual suspects, low hanging fruits, > based on your experience. In fact in my first reply I mentioned the > approach > of disabling optimizer features one-by-one and seeing which one makes the > parse go fast again. This use case would have been great for testing > whether > (or showing that ;) my systematic approach actually works! > > I think it's time to do some bug hunting to reproduce this and to write a > blog entry on my favourite topic :) > > -- > Tanel Poder > http://blog.tanelpoder.com > > > > -----Original Message----- > > From: oracle-l-bounce@xxxxxxxxxxxxx > > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ric Van Dyke > > Sent: 07 May 2009 17:20 > > To: ganstadba@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx > > Subject: RE: Long Parse Time > > > > > ... > > > Yes I would really like to dig into this issue and really > > find the root cause, however I'm likely not going to have the > > opportunity to do that. > > I'm less then fully satisfied that we "threw something at it" > > and it worked without knowing all the gritty details. But as > > we all know there are times where we just have to count our > > blessings and move on. > > > > Again thanks to all that contributed here! > > -- > //www.freelists.org/webpage/oracle-l > > >