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@xxxxxxxxxxxxx]=20 Sent: 20 May 2005 18:39 To: Mladen Gogala Cc: Terry Barnett; oracle-l@xxxxxxxxxxxxx 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 -- //www.freelists.org/webpage/oracle-l