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

On Mar 6, 7:49*am, Dirk Schwarzmann <silent.... (AT) gmx (DOT) de> wrote:
Well, first of all you could try the NL hint.
http://download.oracle.com/docs/cd/B...tsref.htm#5637

You can also look up plan stability in the docs, get the plan from
your db and import it to theirs.
http://download.oracle.com/docs/cd/B...ines.htm#13547

There are many things that could be different. Auto PGA usage would
be the first thing that comes to mind (http://download.oracle.com/docs/
cd/B10501_01/server.920/a96533/memory.htm#48854 ), sort_area_size
( http://download.oracle.com/docs/cd/B...mops.htm#47050
) would be the second. Plenty of other considerations, including size
of SGA, shared server, on and on anon.

Note that all tuning information online is possibly suspect, including
official Oracle docs.

jg
--
@home.com is bogus.
http://news.slashdot.org/article.pl?.../03/06/1326247 "Why
should I spend millions on enterprise apps when I can do it [with
Google] at one-tenth cost and ten times the speed?"
Because google groups glitched not even an hour ago?

Dirk Schwarzmann wrote:
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?

On 06.03.2009 16:49, Dirk Schwarzmann wrote:
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