RE: Cost Based Optimizer
- From: "Terry Barnett" <tbarne@xxxxxxxxxxxxxxxxxxxxxxxxxx>
- To: "Martic Zoran" <zoran_martic@xxxxxxxxx>, "Wolfgang Breitling" <breitliw@xxxxxxxxxxxxx>
- Date: Fri, 20 May 2005 11:09:48 +0100
We do have statistics on the tables in question, but they are 'stale'
but up until now have produced an acceptable execution plan (in terms of
response time).
The change in execution plan seems to point to the CBO knowing something
about the changing table size, but I assumed this sort of information
was gathered from the analyzed statistics for the table and not the
ACTAUL size.
-----Original Message-----
From: Martic Zoran [mailto:zoran_martic@xxxxxxxxx]=20
Sent: 20 May 2005 10:53
To: Terry Barnett; Wolfgang Breitling
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Cost Based Optimizer
Hi Terry,
Do you have statistics on that/these tables?
This is the scenario I am seeing this possible:
- the SQL is removed from the cache (small cache, ...)
- Oracle needed to parse the SQL again
- while parsing your table grows, so the costs changed
when optimizer used the fact how big is the table (I
know that Wolfgang mentioned that the table size is
the one of things Oracle optimizer knows without
having statistics)
I got into my mind that the table growth on the table
without statistics may cause optimizer to change the
plan on the table.
Maybe I got it wrong.
Regards,
Zoran Martic
If this is sort of batch job and the SQL get rid of
from the cache, so Oracle needed to parse and prepare
the execution plan again
--- Terry Barnett <tbarne@xxxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
>=20
>=20
> 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.
>=20
> 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).=3D20
>=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.
>=20
>=20
> Regards,
> Terry
=09=09
__________________________________=20
Do you Yahoo!?=20
Yahoo! Mail - Helps protect you from nasty viruses.=20
http://promotions.yahoo.com/new_mail
=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:
