RE: Cost Based Optimizer
- From: "Terry Barnett" <tbarne@xxxxxxxxxxxxxxxxxxxxxxxxxx>
- To: "Wolfgang Breitling" <breitliw@xxxxxxxxxxxxx>
- Date: Fri, 20 May 2005 10:39:44 +0100
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@xxxxxxxxxxxxx]=20
Sent: 20 May 2005 02:38
To: Terry Barnett
Cc: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
