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

From: jgar the jorrible <joel-garry_at_home.com>
Date: Fri, 6 Mar 2009 16:00:34 -0800 (PST)
Message-ID: <a765d9a0-5af0-48cc-9f93-43356ce5f52f_at_q9g2000yqc.googlegroups.com>



On Mar 6, 7:49 am, Dirk Schwarzmann <silent...._at_gmx.de> 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

Well, first of all you could try the NL hint. http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/hintsref.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/B10501_01/server.920/a96533/outlines.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/B10501_01/server.920/a96533/optimops.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

--
_at_home.com is bogus.
http://news.slashdot.org/article.pl?no_d2=1&sid=09/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?
Received on Fri Mar 06 2009 - 18:00:34 CST

Original text of this message