Re: 9i: Execution plan: Avoid "recursive execution"?
From: Michael Austin <maustin_at_firstdbasource.com>
Date: Fri, 06 Mar 2009 18:55:13 -0600
Message-ID: <dEjsl.15106$as4.1361_at_nlpi069.nbdc.sbc.com>
Dirk Schwarzmann wrote:
> Hi all,
>
> I desperately need your help:
> I have a (complex) SQL statement here which runs fine on our internal
> development database (R 9.2) but crashes with ORA-01652 on the customer
> īs database (which also a R 9.2). We use identical data (got a dump)
> but it seems we have a different db configuration.
>
> I received an execution plan from the customer for this query which is
> very different from the one our db tells us. The customerīs ex. plan
> states "temp table transformation", "recursive execution" and "insert
> statement" (on the temp table, which is quite costly). Our own ex.
> plan does only use nested loops, runs much faster - Iīm pretty sure
> this is due to not writing into the temp table - and of course does
> not crash.
>
> My question now: How can I force the customerīs database to use nested
> loops instead of recursion?
> I already let the customer try out the following compiler hints, but
> without any luck:
>
> select /*+ rule */ ...
> select /*+ ALL_ROWS */ ...
> select /*+ NOREWRITE */ ...
>
> Although we got a different execution plan for every alternative
> (which is quite clear), the compiler always uses recursive execution. I
> īm stuck. Can anyone give my a hint which db configuration parameter
> might be responsible for this since I absolutely have no clue but am
> pretty sure it must be a difference between our two configuration
> settings.
>
> Many thanks in advance,
> Dirk
Date: Fri, 06 Mar 2009 18:55:13 -0600
Message-ID: <dEjsl.15106$as4.1361_at_nlpi069.nbdc.sbc.com>
Dirk Schwarzmann wrote:
> Hi all,
>
> I desperately need your help:
> I have a (complex) SQL statement here which runs fine on our internal
> development database (R 9.2) but crashes with ORA-01652 on the customer
> īs database (which also a R 9.2). We use identical data (got a dump)
> but it seems we have a different db configuration.
>
> I received an execution plan from the customer for this query which is
> very different from the one our db tells us. The customerīs ex. plan
> states "temp table transformation", "recursive execution" and "insert
> statement" (on the temp table, which is quite costly). Our own ex.
> plan does only use nested loops, runs much faster - Iīm pretty sure
> this is due to not writing into the temp table - and of course does
> not crash.
>
> My question now: How can I force the customerīs database to use nested
> loops instead of recursion?
> I already let the customer try out the following compiler hints, but
> without any luck:
>
> select /*+ rule */ ...
> select /*+ ALL_ROWS */ ...
> select /*+ NOREWRITE */ ...
>
> Although we got a different execution plan for every alternative
> (which is quite clear), the compiler always uses recursive execution. I
> īm stuck. Can anyone give my a hint which db configuration parameter
> might be responsible for this since I absolutely have no clue but am
> pretty sure it must be a difference between our two configuration
> settings.
>
> Many thanks in advance,
> Dirk
how is the optimizer_mode configured on each?
Since 9.2.0.8 was the terminal release and is no longer supported (Aug 2008), don't you think it is about time to upgraded to something that is supported for both you and your customers sake? Received on Fri Mar 06 2009 - 18:55:13 CST