Re: Wrong execution plan
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: john.kanagaraj@xxxxxxxxx
- Date: Wed, 10 Jun 2009 19:13:31 +0200
Joerg,
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.
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
