Re: Long Parse Time

>> 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_at_poderc.com> 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_at_freelists.org
> > [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ric Van Dyke
> > Sent: 07 May 2009 17:20
> > To: ganstadba_at_hotmail.com; oracle-l_at_freelists.org
> > 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!
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l