Re: Long Parse Time

  • From: Stefan Knecht <knecht.stefan@xxxxxxxxx>
  • To: ric.van.dyke@xxxxxxxxxx
  • Date: Wed, 6 May 2009 20:40:52 +0200

Ric

Just some spontaneous thoughts:

- Have you run truss on your server process to see if it's for some reason
hanging / spending a lot of time in a certain syscall ?
- Tanel poders snapper, or "OS explain" could be starting points if truss
reveals too much or even nothing useful

Stefan


=========================

Stefan P Knecht
CEO & Founder
s@xxxxxxxx

10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland

Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27
info@xxxxxxxx
http://www.10046.ch

=========================


On Wed, May 6, 2009 at 7:55 PM, Ric Van Dyke <ric.van.dyke@xxxxxxxxxx>wrote:

>  Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
> Production
>
> System name:   SunOS
>
> Release:           5.10
>
>
>
> I’m working on a query that takes about 30 seconds to parse.  Run time is
> not and issue, it runs in .12 seconds once parsed. Its the parse time is a
> problem.  Yes I know the mantra “parse once execute many”, just forget that
> for a moment and assume that the parsing of this statement must be reduced.
>
>
>
> The query original was based on a view that was based on several views, and
> finally hit the base tables 2-3 layers down.  I’ve got the query now only
> hitting the base tables and it still takes about 30 seconds to parse.  It
> hits 8 tables which I would not expect to be a huge deal.
>
>
>
> The 10053 trace file is over 200 Megs.  Reading thru it has been
> interesting but still less then clear why this thing is taking that long to
> parse.  Using a RULE hint in one of the views did reduce the parse time.
>
>
>
> None of the predicates are very complex, there are two correlated
> sub-queries, one like and the others are run of the mill equality
> predicates.
>
>
>
> My searches on Metalink/Google have not turned up any bugs or the like.
>
>
>
> Would anyone happen to have to ideas on what would cause the optimizer to
> take this long to parse a statement?  Is anyone aware of something that
> would cause the optimizer to do something like this?  I’m just looking for
> some clues as to what to investigate.  I was pretty sure it was a view
> resolution issue, but now that seems not the case.
>
>
>
> -----------------------
>
> Ric Van Dyke
>
> Hotsos Enterprises
>
> -----------------------
>
>
>
> *Hotsos Symposium *
>
> *March 7 – 11, 2010 *
>
> *Be there.*
>
>
>
>
>
>
>

Other related posts: