Re: Cost Based Optimizer

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

Mladen Gogala wrote:

> Wolfgang Breitling wrote:
>

>> 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.
>>

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l