RE: Cost Based Optimizer

We are running version 9.2.0.1 on a Sunfire V880 (6 * 1.2GHZ CPUs 24Gb memory). DB parameter optimizer_dynamic_sampling is set to 1.

The particular SQL statements in question do use bind variables. Typically what's happening is that fast nested loop range scan joins are turning into full table scan hash joins (for relatively small resulting record sets).=20

As already mentioned, we deliberately keep statistics 'stale' and DB parameters constant when performance is at an acceptable level. The only varying factor (I am aware of) is the size of the tables i.e. the join tables are constantly growing with 1000's of new record inserts per day.

Regards,
Terry

-----Original Message-----
From: Wolfgang Breitling [mailto:breitliw_at_centrexcc.com]=20 Sent: 20 May 2005 02:38
To: Terry Barnett
Cc: oracle-l_at_freelists.org
Subject: Re: Cost Based Optimizer

First of all you don't give any information such as Oracle version.=20 Secondly, the CBO is basically a calculator. Feed in the same numbers and=20
you will get the same result, Conversely, if you get different results,=20 then some of the inputs were different. Without knowing any more about the=20
specifics like Oracle version, the sql (e.g. id it using literals of bind=20
variables), the type and structure of the tables involved, I don't want to=20
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=20

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
This email and any files transmitted
with it are confidential and intended
solely for the use of the individual
or entity to whom they are addressed.

If you have received this email in=20
error please notify Landmark=20
Information Group on +44(0) 1392=20
441700.

For more information about the=20
Landmark Information Group visit
http://www.landmark-information.co.uk

This email and any attachments have
been scanned for viruses and to the
best of our knowledge are clean.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

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