RE: Cost Based Optimizer

From: Terry Barnett <tbarne_at_landmark-information.co.uk>
Date: Fri, 20 May 2005 11:09:48 +0100
Message-ID: <A57A0C7C72F4B144BA0B1AB7DB2AFE97539CF9@exmx04.corp.edrlandmark.net>

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_at_yahoo.com]=20 Sent: 20 May 2005 10:53
To: Terry Barnett; Wolfgang Breitling
Cc: oracle-l_at_freelists.org
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_at_landmark-information.co.uk> 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

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

Received on Fri May 20 2005 - 06:14:33 CDT