RE: Long Parse Time
- From: "Tanel Poder" <tanel@xxxxxxxxxx>
- To: <ric.van.dyke@xxxxxxxxxx>, <ganstadba@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 7 May 2009 17:46:00 +0300
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
Other related posts:
