RE: Cost Based Optimizer

From: Terry Barnett <tbarne_at_landmark-information.co.uk>
Date: Sun, 22 May 2005 20:19:14 +0100
Message-ID: <A57A0C7C72F4B144BA0B1AB7DB2AFE97539D02@exmx04.corp.edrlandmark.net>

The suggestion of the use of different bind values (after cursor aging or invalidation) causing the change in execution plan seems a very logical one. This certainly fits the sort of behavior that we are experiencing.=20

Thanks for all the comments.

Regards,
Terry

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

Just like index or table access costs are influenced by a number of=20 factors (i.e. object statistics), other operations such as hash joins,=20 SM joins and plain sorts are influenced by a number of factors, just=20 like the actual operation itself. In order to make sorts faster, you=20 give it a larger sort_area_size. The optimizer knows that as well.=20 Larger sort_area_size, hash_area_size, hash_multiblock_IO_count (now an=20 underscore parameter), etc affect the cost of those operations and thus=20 can affect the plan chosen. However, unless you change any of those=20 parameters, the cost of the related operations does not change and thus=20 will not cause a change in plan.
Right now my money is on reparsing with different BV values causing the=20 change in plan. Reparsing could be caused by the sql aging out of the=20 shared pool or being invalidated by some action.

Mladen Gogala wrote:

> Wolfgang Breitling wrote:
>=20

>> The scenario could be that over=20
>> night the sql with the plan ages out of the shared pool and the next=20
>> morning it gets re-parsed by the first person to use that sql. Then,=20
>> because of the BV and cursor sharing, the plan gets locked-in until=20
>> the sql ages out again.
>>

> Wolfgang, can you comment a bit on Christian's statement about memory=20
> allocation influencing SQL
> execution plan?
>=20

--=20
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

=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 Sun May 22 2005 - 15:24:07 CDT