jump to navigation

Dealing with Bind Issues December 1, 2009

Posted by mwidlake in Meeting notes, performance.
Tags: , ,
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.

About these ads

Comments»

1. coskan - December 1, 2009

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.

2. mwidlake - December 1, 2009

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.

3. Richard Smith - December 1, 2009

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
4. Richard Smith - December 1, 2009

Er, except that the LIKE would be an equality,..,.

5. radino - December 2, 2009

I think you don’t need cardinality hint..

Comment should be enough to get 2 different plans (based on peeked value)..

if :status in (1,2,3) then
	  select /*+ stm1 */
	  from table_1 t1
	      , table_t t2
	  where t1.status=:status
	  and....;
	else
	  select /* stm2 */
	  from table_1 t1
	      , table_t t2
	  where t1.status=:status
	  and....;
	end;
mwidlake - December 2, 2009

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.

6. radino - December 2, 2009

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..

mwidlake - December 2, 2009

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

7. Bernard Polarski - December 2, 2009

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.

8. Log Buffer #171: a Carnival of the Vanities for DBAs | The Pythian Blog - December 4, 2009

[...] 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 [...]

9. Blogroll Report 27/11/2009-04/12/2009 « Coskan’s Approach to Oracle - December 18, 2009

[...] 6-Alternative workarounds for plans suffering from bind peeking? Martin Widlake-Dealing with Bind Issues [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 152 other followers

%d bloggers like this: