9i: Execution plan: Avoid "recursive execution"?

From: Dirk Schwarzmann <silent.rob_at_gmx.de>
Date: Fri, 6 Mar 2009 07:49:07 -0800 (PST)
Message-ID: <04f3fad1-08dd-4c36-a759-a9756232163f_at_n30g2000vba.googlegroups.com>

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 Received on Fri Mar 06 2009 - 09:49:07 CST

Original text of this message