RE: Long Parse Time

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