RE: Long Parse Time
- From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 07 May 2009 09:48:40 +0200
Ric,
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:
1. Use subquery factoring: It's not well known but at least up to 11.1.0.6 (I
haven't tested it in 11.1.0.7 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:
http://oracle-randolf.blogspot.com/2008/01/subquery-factoring-and-cost-based-query.html
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.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
> Subject: Long Parse Time
> Date: Wed, 6 May 2009 12:55:52 -0500
> From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
>
> Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
> Production
>
> 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.
>
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
