Dealing with Bind Issues December 1, 2009

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:

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.

Like this:

LikeLoading...

Related

Only problem is dependency on hints and writing more code depending on the number of values. Nice thing to know but I think usability is very limited if there are more joins more execution plan possibilities.

It’s not so much the use of the cardinality hint as just having two statements that are different, such that one works well for one data set and the other for another data set. You still get the benefit of binds (ie reducing the number of cursors in the SGA), which is something I really liked about the idea. I just used cardinality hints as, off the top of my head, it seemed like a very good way to tell the CBO exactly what is different about the two versions.

In fact, thinking about it, if the cardinality hint does not have the effect you want, maybe it is not a bind/histogram issue with the predicate you think it is.

Cardinality hints I view as “nice” hints. You are not forcing the CBO down a route or blocking it from considering access paths, you are giving it extra information.

Changing the code can be an issue, but if the problem is serious, changing the code and commenting WHY you have the two versions is a very targetted fix and a documented one, whereas deleting the histograms (one of the other options) is a hidden fix and could mess up other code.

Yeah, as I write this long reply, I’m starting to seriously warm to the idea…

If you need to do something similar in a view, the if/then/else can be replaced with a UNION ALL – and allow Oracle to filter out the unnecessary sub-query. I’ve come across a number of “client” queries which do things like:

SELECT stuff
FROM tables
WHERE field LIKE
NVL('%' || :string || '%', field)
AND other_clauses

which wreaks any chance of the optimiser using any index on field. The UNION all approach allows the index on field to be used if it can be:

SELECT stuff
FROM tables
WHERE :string IS NULL
AND other_clauses
UNION ALL
SELECT stuff
FROM tables
WHERE :string IS NOT NULL
AND field LIKE '%' || :string || '%'
AND other_clauses

It’s certainly better than flushing the whole shared pool and I prefer it to that option, thanks for the tip. But it will not flush the code if it is “in flight”, so you would need to check afterwards in v$sqlarea to see the first_load_timt to know you got a new plan.

There is no guarantee that the next bind variable will give you the plan you want either.

First time I read 11g will vary plan following cardinality, I thought immediately we were going towards interesting time (read bloody:p). Given the Universal law : ‘Advisers are not the Payers’, if at the same time Advisers are allowed to take initiatives in behalf of the Payers, we should see poping around here and there nice stories of post-mortem analysis.