RE: Long Parse Time
Date: Thu, 07 May 2009 09:48:40 +0200
I've recently had an issue with a query that took up to 20 mins (!) to parse, containing simple equality joins to multiple table, inline views and uncorrelated subqueries.
In addition to the suggestions that you've already got two things that might help to reduce the parse time significantly:
- Use subquery factoring: It's not well known but at least up to 220.127.116.11 (I haven't tested it in 18.104.22.168 yet) there is a significant difference between using (inline) views (or no view at all) and doing the same with subquery factoring. Using subquery factoring disables certain optimizer paths, in particular some part of the Cost Based Query Transformation/Complex view merging, see e.g. here:
In your case this might help to reduce the parse time since the CBQT feedback loop might take a significant amount of parse time.
2. Combine above with "NO_MERGE" hints: Using the simple NO_MERGE hint in the factored out subquery reduces the number of options to evaluate even further
Using this approach I was able to cut down the parse time to 5-7 seconds and the execution time to 1 second, which was acceptable in this case given the initial parse time of several minutes.
Using the undocumented parameters mentioned by Tanel you should be able to quickly find out if above mentioned features are causing the issues (Cost Based Query Transformation, Complex View Merging, etc.) and if yes, the outlined rewrites might help in this matter without the need to fiddle with the undocumented parameters.
Oracle related stuff blog:
> Subject: Long Parse Time
> Date: Wed, 6 May 2009 12:55:52 -0500
> From: "Ric Van Dyke" <ric.van.dyke_at_hotsos.com>
> Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
> 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
> None of the predicates are very complex, there are two correlated
> sub-queries, one like and the others are run of the mill equality
> 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.
Verschicken Sie SMS direkt vom Postfach aus - in alle deutschen und viele auslšndische Netze zum gleichen Preis!
https://produkte.web.de/webde_sms/sms Received on Thu May 07 2009 - 02:48:40 CDT