Re: 9i: Execution plan: Avoid "recursive execution"?
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Sat, 07 Mar 2009 12:36:27 +0100
Message-ID: <71f4j7Fkm6a4U1_at_mid.individual.net>
On 06.03.2009 16:49, 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.
Date: Sat, 07 Mar 2009 12:36:27 +0100
Message-ID: <71f4j7Fkm6a4U1_at_mid.individual.net>
On 06.03.2009 16:49, 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.
Maybe you "only" have different statistics. You could export customer's statistics and import them into one of your test databases to try out different settings and hints instead of using the production DB for testing.
Did you verify that statistics in customer's database are current and ok?
And as a quick fix you could give your temp tablespace more space which might buy you some time for analysis and problem resolution.
Kind regards
robert Received on Sat Mar 07 2009 - 05:36:27 CST