Dealing with Bind Issues December 1, 2009
Posted by mwidlake in Meeting notes, performance.Tags: histograms, Meeting, performance
trackback
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.
I never thought to use cardinality feedback for solving bind peeking.
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.
Thanks for sharing. Hope to see you tomorrow.
Hi Coskan,
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…
I can’t wait to find out it does not work, haha🙂
Looking forward to meeting you tomorrow Coskan.
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_clauseswhich 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:
Er, except that the LIKE would be an equality,..,.
I think you don’t need cardinality hint..
Comment should be enough to get 2 different plans (based on peeked value)..
Thanks for that Radion. That is true, each statement would get a different plan and you would expect, if your “If” condition was identifying the binds correctly, the plan should be right.
btw there is a nice trick how to purge ALL(!) plans from SGA for given table:
comment on table t is ‘…’
of course there is:
@?/rdbms/admin/dbmspool (see metalink note 751876.1)
alter session set events ‘5614566 trace name context forever’;
exec sys.dbms_shared_pool.purge(’&addr, &hash_val’,’c’)
but you need more privileges for this..
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.
Cheers,
Martin
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.
[…] Widlake examines the business of dealing with bind issues, having been shown at UKOUG the fourth solution to the problem of mixing bind variables and […]
[…] 6-Alternative workarounds for plans suffering from bind peeking? Martin Widlake-Dealing with Bind Issues […]