Re: Cost Based Optimizer
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: Mladen Gogala <mgogala@xxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 20 May 2005 11:39:04 -0600
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.
>>
> Wolfgang, can you comment a bit on Christian's statement about memory
> allocation influencing SQL
> execution plan?
>
--
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
