Tuesday, 7 October 2008

Histograms and bind variables

This post references a post on Richard Foote's Blog, specifically this post on
DBMS_STATS METHOD_OPT default behaviour changed in 10g.

I have been doing a bit of testing in order to determine if I should wipe out all of my histograms. The issue is that we have almost 90,000 histograms that are probably using valuable SGA that could be used for better purposes.

Our JDE system uses bind variables everywhere, and we run with _optim_peek_user_binds set to false (no peeking) to avoid issues like Alberto Dell'Era mentions here, where depending on the first query parsed you may get a good or a bad plan.

With the table Richard mentions I see:


No histogram
explain plan for select * from hist_test where id1 >1; <- FULL TABLE SCAN
explain plan for select * from hist_test where id1 > 1000000; <- FULL TABLE SCAN

No histogram and bind variables
var n1 number;
exec :n1 := 1;
var n2 number;
exec :n2 := 1000000;
explain plan for select * from hist_test where id1 > :n1; <- INDEX RANGE SCAN
explain plan for select * from hist_test where id1 > :n2; <- INDEX RANGE SCAN

254 bucket histogram
explain plan for select * from hist_test where id1 >1; <- FULL TABLE SCAN
explain plan for select * from hist_test where id1 > 1000000; <- INDEX RANGE SCAN

254 bucket histogram and bind variables
explain plan for select * from hist_test where id1 > :n1; <- INDEX RANGE SCAN
explain plan for select * from hist_test where id1 > :n2; <- INDEX RANGE SCAN

The fact that the index range scan is picked for both bind values whether a histogram exists or not seems to indicate that the histogram will make no difference to us since I told the optimiser it cannot peek (it does not matter which is value is used first either).

It also means, that if we have tables like this outlier one, we will be getting sub optimal performance most of the time (IE index rather than FTS for a query like id1 > :n1 with :n1 = 1). I will look into this a bit deeper in another blog entry and put a pointer here when I do.

It also looks like the SQL Tuning advisor will not suggest a better plan for this either given the bind variables.

In any case, I now am comfortable that the histograms do not seem to do us any good as far as plans go when I use bind variables and do not allow peeking.

What I am not 100% sure of is that the histograms are not used for calculating the cost of joins. I would assume not, as the optimizer should not know what is in the bind variables anyway, so should not make use of them for calculating joins. But I will have to test that too, and will update this when I get a chance to do that.

Another question is if I turn on peeking can I do better? In my case, no, but I will look at that in another post too (see HERE).

Comments and observations are welcomed, doubly so if I learn something from them! 8-)

No comments: