Hello Experts,
I read articles about bind variable peeking written by Tom Kyte. First I read Ask Tom: On Tuning by Tracing Then I read The Tom Kyte Blog: Tuning with sql_trace=true... Then I read The Tom Kyte Blog: Sql_trace=true, Part Two...
I learned new things that I hadn't know before.
As far as I understand from the first article (Ask Tom: On Tuning by Tracing), in Oracle 11g there is a very good feature called bind variable peeking that the optimizer checks for the best execution plan for each bind variables rather than use only one execution plan (It will be the first hard parsed query). So this might be a good feature.
However, I got confused by the following quotte
But when bind variable peeking doesn't work "nicely" and Oracle Database 11g is not a viable solution for you what can you do to get around bind variable peeking? I'll go through the ideas one by one
I am wondering what did he mean when he said "bind variable peeking doesn't work 'nicely' "? Why it does not work "nicely"? If this is bad feature why he want to disable it?
And I was also astonished by the following quote,
Don't gather statistics that cause the plan to flip-flop. If there is only one plan that the optimizer would ever choose, then bind variable peeking will have no effect. If you were to execute the above example without gathering histograms, the plan would not flip-flop.
If my data is skewed, why I don't use histograms? I am just tring to understand that what makes me not use statistics?
Please forgive me if my questions are meaningless, I am just trying to learn it.
Thanks a lot.
Regards.