Re: Wrong execution plan
Date: Wed, 10 Jun 2009 19:13:31 +0200
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.
John Kanagaraj wrote:
>> 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.