Re: Wrong execution plan

    To add to what John said, some strange things also happen when values fall OUTSIDE the hi/lo range. I've seen something very similar one year ago. Touching the code was forbidden, it ended up with an outline. Values outside the known range suddenly become super-selective for the optimizer. Add the fact that a 2-column index is probably smaller (fewer blocks to inspect) than a 3-column index, a pinch of clustering factor, and the choice of the "wrong" index may have been perfectly logical when the query was parsed.

HTH SF

John Kanagaraj wrote:
> Joerg,
>
>
>> I resolved the actual situation with removing the plan from the shared
>> pool. After this the optimizer generated a plan with the primary key.
>>
>
> Be aware that bind peeking occurs even when Histograms are not present
> - changing LO/HI values also causes this and given the fact that the
> right plan was chosen when you flushed this from the shared pool, it
> is a probable cause. How are you collecting your stats, and what is
> the rate of change in lo/hi values? Investigate this line of thought
> and you will probably arrive at he root cause of the problem. We would
> appreciate it if you found anything and posted it back to the list.
>
>

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