Re: Long Parse Time
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: ric.van.dyke@xxxxxxxxxx
- Date: Wed, 06 May 2009 20:38:12 +0200
Ric,
Some random thoughts:
- Bizarre subqueries.
* When two subqueries are dependent on each other (for instance date_col
= (select max(blah) ...) and other suquery correlated to date_col) it
often leads to interesting execution paths.
* I also remember seeing the optimizer going nuts on a CORRELATED in ()
subquery. Not always something you spot immediately when inspecting the
query, mostly because you don't expect it (now I do, but the first time
it caught me by surprise).
- Manic activity within the library cache
- No obvious best way to do it. If you have tons of mildly as efficient
ways to perform a query (and with 8 tables the number of combinations
isn't negligible) Oracle may waste time trying to find the best one.
Cutting on the number of iterations allowed to the optimizer might help.
HTH
Stéphane Faroult
Ric Van Dyke 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.**
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
