RE: Cost Based Optimizer
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: tbarne@xxxxxxxxxxxxxxxxxxxxxxxxxx
- Date: Fri, 20 May 2005 05:40:46 -0600
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
