RE: Cost Based Optimizer

Wolfgang and others,

Assuming 9i or higher, and work area size policy of auto and pga aggregate target set, could this cause a plan change depending upon the workload on the machine? Let's say the preferred access path might include a hash join between two objects, then feeding into another hash join on another object. With heavy pressure on the PGA, approaching the target, it could decide, for example, it can't serve up the amount of memory needed for an efficient hash join, so it drops into a nested loops?

I seem to remember others discussing this, maybe on this list, or maybe at one of the HOTSOS conferences.

I had a case where the parameters stayed the same, the data was static, and the stats were static. Testing with a 10046 trace I verified we got the desired plan. Then scheduled to run at night time, still tracing, when a lot of large batch jobs were running. The hash joins, according to the trace, dropped into nested loops. Ran again the next day (with the machine lightly loaded) and the good plan again. Another test overnight and the poor plan.

I started to suspect that pga aggregate target played a role. I then tried to duplicate the situation, get a 10053 (would it have the details?), look at memory usage, etc, when the plan goes bad, but never could get the bad plan again. I've wanted to build a test case showing this but other more pressing work took precedence. But I'm pretty sure someone on this list has brought this up before, but I don't remember if they had a test case demonstrating it.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wolfgang Breitling
> Sent: Thursday, May 19, 2005 8:38 PM
> To: tbarne_at_landmark-information.co.uk
> Cc: oracle-l_at_freelists.org
> Subject: Re: Cost Based Optimizer
>
>
> First of all you don't give any information such as Oracle version.
> Secondly, the CBO is basically a calculator. Feed in the same numbers and
> you will get the same result, Conversely, if you get different results,
> then some of the inputs were different. Without knowing any more
> about the
> specifics like Oracle version, the sql (e.g. id it using literals of bind
> variables), the type and structure of the tables involved, I
> don't want to
> even begin speculating.
>
> At 02:04 PM 5/19/2005, Terry Barnett wrote:
>
>
> >I am having issues with execution plans changing even though the
> >statistics on tables are kept static. Can anyone give me any information
> >on the dynamic factors which are taken into account by the CBO when
> >determining an execution plan and how one could detect that a plan may
> >be about to change.
>
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com
>
> --
> http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l