RE: Cost Based Optimizer

Do you have histograms on any predicate columns that use bind variables? Depending on the actual bind value at the time of the parse you can easily get different plans. The scenario could be that over night the sql with the plan ages out of the shared pool and the next morning it gets re-parsed by the first person to use that sql. Then, because of the BV and cursor sharing, the plan gets locked-in until the sql ages out again.

At 03:39 AM 5/20/2005, Terry Barnett wrote:

>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

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

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