Dealing with Bind Issues December 1, 2009Posted by mwidlake in Meeting notes, performance.
Tags: histograms, Meeting, performance
One of the presentations I have seen today was on handling bind values. I can’t say it was, for me, the best I have seen this week, I’ve done a lot of work on binds in the past so I had come across most of the material before. Should I have bothered?
Well, there was one little gem in there which struck me as very simple and potentially very effective.
Bind variables and histograms are not a good mix, as has been commented on many, many times. In essence, if your data in a column is skewed so that some values match very few records and others match a large number, when oracle sees a SQL statement with a bind value being compared to that column, it peeks at the first value being passed in with the bind and uses it to decide on the plan (this is pre 11G, by the way).
That plan is then used for every execution of that sql statement until it is thrown out the SGA. Which is jolly unfortunate if all the values subsequently passed in via the bind do not suit the plan.
The solutions to this usually boil down to one of three approaches; remove the histograms on the column in question so that all values are treated equally;stop it being a bind/prevent bind peeking; force the “bad” plan out of the SGA and hope the next parse gets a better plan.
All have their merits and drawbacks.
Well, in this presentation there was a fourth solution. Something like this:
if :status in (1,2,3) then select /*+ cardinality (t1 100000) */ from table_1 t1 , table_t t2 where t1.status=:status and....; else select /*+ cardinality (t1 100) */ from table_1 t1 , table_t t2 where t1.status=:status and....; end;
I might be missing something blindingly obvious here (and this might be a common solution that has just passed me by), but it seems to me to be a simple and effective solution that could be used in many situations.
I also learnt that it is rare not to find at least one good thing out of any presentation, so long as you keep paying attention.