Bind Variable Peeking – Drives Me Nuts!
In the constant battle to provide consistent performance, Oracle took a giant step backwards with the 9i version by introducing an “Enhancement” called Bind Variable Peeking. I’ll explain what I mean in a minute, but first a bit of history.
When Oracle introduced histograms in 8i, they provided a mechanism for the optimizer to recognize that the values in a column were not distributed evenly. That is, in a table with 100 rows and 10 distinct values, the default assumption the optimizer would make, in the absence of a histogram, would be that no matter which value you picked – you would always get 100/10 or 10 rows back. Histograms let the optimizer know if that was not the case. The classic example would be 100 records with 2 distinct values where one value, say “Y”, occurred 99 times and the other value, say “N”, occurred only 1 time. So without a histogram the optimizer would always assume that whether you requested records with a “Y” or an “N”, you would get half the records back (100/2 = 50). Therefore you always want to do a full table scan as opposed to using an index on the column. A histogram, assuming it was accurate (we’ll come back to that later), would let the optimizer know that the distribution was not normal (i.e. not spread out evenly – also commonly called skewed) and that a “Y” would get basically the whole table, while an “N” would get only 1%. This would allow the optimizer to pick an appropriate plan regardless of which value was specified in the Where Clause.
So let’s consider the implications of that. Would that improve the response time for the query where the value was “Y”. The answer is no. In this simple case, the default costing algorithm is close enough and produces the same plan that the histogram produces. The full table scan takes just as long whether the optimizer thought it was getting 50 rows or 99 rows. But what about the case where we specified the value of “N”. In this case, with a histogram we would pick up the index on that column and presumably get a much better response time than the full table scan. This is an important point. Generally speaking it is only the outliers, the exceptional cases if you will, where the histogram really makes a difference.
So at first glance, all appeared well with the world. But there was a fly in the ointment. You had to use literals in your SQL statements for the optimizer to be able use the histograms. So you had to write your statements like this:
SELECT XYZ FROM TABLE1 WHERE COLUMN1 = ‘Y’;
SELECT XYZ FROM TABLE1 WHERE COLUMN1 = ‘N’;
Not a problem in our simple example, because you only have two possibilities. But consider a statement with 2 or 3 skewed columns, each with a couple of hundred distinct values. The possible combinations could quickly grow into the millions. Not a good thing for the shared pool.
Enter our star: Bind Variable Peeking, a new feature introduced in 9i that was added to allow the optimizer to peek at the value of bind variables and then use a histogram to pick an appropriate plan, just like it would do with literals. The problem with the new feature was that it only looked at the variables once, when the statement was parsed. So let’s make our simple example a little more realistic by assuming we have a 10 million row table where 99% have a value of “Y” and 1% have a value of “N”. So in our example, if the first time the statement was executed it was passed a “Y”, the full table scan plan would be locked in and it would be used until the statement had to be re-parsed, even if the value “N” was passed to it in subsequent executions.
So let’s consider the implication of that. When you get the full table scan plan (because you passed a “Y” the first time) it behaves the same way no matter what which value you pass subsequently. Always a full table scan, always the same amount of work and the same basic elapsed time. From a user standpoint that seems reasonable. The performance is consistent. (this is the way it would work without a histogram by the way) On the other hand, if the index plan gets picked because the parse occurs with a value of “N”, the executions where the value is “N” will be even faster than they were before, but the execution with a value of “Y” will be incredibly slow. This is not at all what the user expects. They expect the response time to be about the same every time they execute a piece of code. And this is the problem with bind variable peeking. It’s basically just Russian Roulette. It just depends on what value you happen to pass the statement when it’s parsed (which could be any execution by the way).
So is Bind Variable Peeking a feature or a bug? Well technically it’s not a bug because it works the way it’s designed. I just happen to believe that it was not a good decision to implement it that way. But what other choices did the optimizer development group have?
- They could have evaluated the bind variables and re-parsed for every execution of every statement using bind variables. This would eliminate the advantage of having bind variables in the first place and would never work for high transaction systems. So it was basically not an option.
- They could have just said no, and made us use literals in order to get the benefit of histograms (probably not a bad option in retrospect – the fact that they added _optim_peek_user_binds probably means that they decided later to give us that option via setting this hidden parameter).
- They could have implemented a system where they could identify statements that might benefit from different plans based on the values of bind variables. Then peek at those variables for every execution of those “bind sensitive” statements (sound familiar? – that’s what they finally did in 11g with Adaptive Cursor Sharing).
So why is it such a pervasive problem? And I do believe it is a pervasive problem with 10g in particular. A couple of reasons come to mind:
- We’ve been taught to always use bind variables. It’s a best practice which allows SQL statements to be shared, thus eliminating a great deal of work/contention. Using bind variable is an absolute necessity when building scalable high transaction rate systems. (of course that doesn’t mean that you can’t bend the rule occasionally)
- 10g changed it’s default stats gathering method to automatically gather histograms. So in a typical 10g database there are a huge number of histograms, many of them inappropriate (i.e. on columns that don’t have significantly skewed distributions) and many of them created with very small sample sizes causing the histograms to be less than accurate. Note that 11g appears to be better on both counts – that is to say, 11g seems to create fewer inappropriate histograms and seems to create much more accurate histograms with small sample sizes. But the jury is still out on 11g stats gathering as it has not been widely adopted at this point in time.
- In my humble opinion, Bind Variable Peeking is not that well understood. When I talk to people about the issue, they usually have heard of it and have a basic idea what the problem is, but their behavior (in terms of the code they write and how they manage their databases) indicates that they don’t really have a good handle on the issue.
So what’s the best way to deal with this issue? Well recognizing that you have a problem is the first step to recovery, so being able to identify that you have a problem with plan stability is an appropriate first step. Direct queries against the Statspack or AWR tables are probably the best way to identify the issue. I’ve posted a couple of scripts that I find useful for this purpose previously – (unstable_plans.sql, awr_plan_stats.sql, awr_plan_change.sql). What you’re looking for is statements that flip flop back and forth between 2 or more plans. Note that there are other reasons for statements to change plans, but Bind Variable Peeking is the number one suspect. Here’s an example of their usage:
SQL> @unstable_plans
SQL> break on plan_hash_value on startup_time skip 1
SQL> select * from (
2 select sql_id, sum(execs), min(avg_etime) min_etime, max(avg_etime) max_etime, stddev_etime/min(avg_etime) norm_stddev
3 from (
4 select sql_id, plan_hash_value, execs, avg_etime,
5 stddev(avg_etime) over (partition by sql_id) stddev_etime
6 from (
7 select sql_id, plan_hash_value,
8 sum(nvl(executions_delta,0)) execs,
9 (sum(elapsed_time_delta)/decode(sum(nvl(executions_delta,0)),0,1,sum(executions_delta))/1000000) avg_etime
10 -- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
11 from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
12 where ss.snap_id = S.snap_id
13 and ss.instance_number = S.instance_number
14 and executions_delta > 0
15 and elapsed_time_delta > 0
16 group by sql_id, plan_hash_value
17 )
18 )
19 group by sql_id, stddev_etime
20 )
21 where norm_stddev > nvl(to_number('&min_stddev'),2)
22 and max_etime > nvl(to_number('&min_etime'),.1)
23 order by norm_stddev
24 /
Enter value for min_stddev:
Enter value for min_etime:
SQL_ID SUM(EXECS) MIN_ETIME MAX_ETIME NORM_STDDEV
------------- ---------- ----------- ----------- -------------
1tn90bbpyjshq 20 .06 .24 2.2039
0qa98gcnnza7h 16 20.62 156.72 4.6669
7vgmvmy8vvb9s 170 .04 .39 6.3705
32whwm2babwpt 196 .02 .26 8.1444
5jjx6dhb68d5v 51 .03 .47 9.3888
71y370j6428cb 155 .01 .38 19.7416
66gs90fyynks7 163 .02 .55 21.1603
b0cxc52zmwaxs 197 .02 .68 23.6470
31a13pnjps7j3 196 .02 1.03 35.1301
7k6zct1sya530 197 .53 49.88 65.2909
10 rows selected.
SQL> @find_sql
SQL> select sql_id, child_number, plan_hash_value plan_hash, executions execs,
2 (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
3 buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,
4 sql_text
5 from v$sql s
6 where upper(sql_text) like upper(nvl('&sql_text',sql_text))
7 and sql_text not like '%from v$sql where sql_text like nvl(%'
8 and sql_id like nvl('&sql_id',sql_id)
9 order by 1, 2, 3
10 /
Enter value for sql_text:
Enter value for sql_id: 0qa98gcnnza7h
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ------ ---------- ------------ ------------- ------------ ------------------------------------------------------------
0qa98gcnnza7h 0 568322376 3 9.02 173,807 select avg(pk_col) from kso.skew where col1 > 0
SQL> @awr_plan_stats
SQL> break on plan_hash_value on startup_time skip 1
SQL> select sql_id, plan_hash_value, sum(execs) execs, sum(etime) etime, sum(etime)/sum(execs) avg_etime, sum(lio)/sum(execs) avg_lio
2 from (
3 select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
4 nvl(executions_delta,0) execs,
5 elapsed_time_delta/1000000 etime,
6 (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
7 buffer_gets_delta lio,
8 (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
9 from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
10 where sql_id = nvl('&sql_id','4dqs2k5tynk61')
11 and ss.snap_id = S.snap_id
12 and ss.instance_number = S.instance_number
13 and executions_delta > 0
14 )
15 group by sql_id, plan_hash_value
16 order by 5
17 /
Enter value for sql_id: 0qa98gcnnza7h
SQL_ID PLAN_HASH_VALUE EXECS ETIME AVG_ETIME AVG_LIO
------------- --------------- ------------ -------------- ------------ --------------
0qa98gcnnza7h 568322376 14 288.7 20.620 172,547.4
0qa98gcnnza7h 3723858078 2 313.4 156.715 28,901,466.0
SQL> @awr_plan_change
SQL> break on plan_hash_value on startup_time skip 1
SQL> select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
2 nvl(executions_delta,0) execs,
3 (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
4 (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
5 from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
6 where sql_id = nvl('&sql_id','4dqs2k5tynk61')
7 and ss.snap_id = S.snap_id
8 and ss.instance_number = S.instance_number
9 and executions_delta > 0
10 order by 1, 2, 3
11 /
Enter value for sql_id: 0qa98gcnnza7h
SNAP_ID NODE BEGIN_INTERVAL_TIME SQL_ID PLAN_HASH_VALUE EXECS AVG_ETIME AVG_LIO
---------- ------ ------------------------------ ------------- --------------- ------------ ------------ --------------
21857 1 20-MAR-09 04.00.08.872 PM 0qa98gcnnza7h 568322376 1 31.528 173,854.0
22027 1 27-MAR-09 05.00.08.006 PM 0qa98gcnnza7h 1 139.141 156,807.0
22030 1 27-MAR-09 08.00.15.380 PM 0qa98gcnnza7h 3 12.451 173,731.0
22031 1 27-MAR-09 08.50.04.757 PM 0qa98gcnnza7h 2 8.771 173,731.0
22032 1 27-MAR-09 08.50.47.031 PM 0qa98gcnnza7h 3723858078 1 215.876 28,901,466.0
22033 1 27-MAR-09 08.57.37.614 PM 0qa98gcnnza7h 568322376 2 9.804 173,731.0
22034 1 27-MAR-09 08.59.12.432 PM 0qa98gcnnza7h 3723858078 1 97.554 28,901,466.0
22034 1 27-MAR-09 08.59.12.432 PM 0qa98gcnnza7h 568322376 2 8.222 173,731.5
22035 1 27-MAR-09 09.12.00.422 PM 0qa98gcnnza7h 3 9.023 173,807.3
9 rows selected.
So back to the question, what’s the best way to deal with the issue. In general, the best way to eliminate Bind Variable Peeking is as follows:
- Only create histograms on skewed columns.
- Use literals in where clauses on columns where you have histograms and want to use them. Note that it’s not necessary to use literals for every possible value of a skewed column. There may be only a few outlier values that result in significantly different plans. With a little extra code you can use literals for those values and bind variables for the rest of the values that don’t matter.
- If you can’t modify the code, consider turning off Bind Variable Peeking by setting the _OPTIM_PEEK_USER_BINDS parameter to false. You won’t get the absolute best performance for every possible statement, but you will get much more consistent performance, which is, in my opinion, more important than getting the absolute best performance. Keep in mind that this is a hidden parameter and so should be carefully tested and probably discussed with Oracle support prior to implementing it in any production system.
- You can also consider stronger methods of forcing the optimizer’s hand such as Outlines (see my previous posts on Unstable Plans and on Outlines). This option provides a quick method of locking in a single plan, but it’s not fool proof. Even with outlines, there is some possibility that the plan can change. Also note that this option is only palatable in situations where you have a relatively small number of problem SQL statements.
- Upgrade to 11g and let Adaptive Cursor Sharing take care of all your problems for you (don’t bet on it working without a little effort – I’ll try to do a post on that soon).
In summary, using literals with histograms on columns with skewed data distributions are really the only effective way to deal with the issue and still retain the ability for the optimizer to choose the absolute best execution plans. However, if circumstances prevent this approach, there are other techniques that can be applied. These should be considered temporary fixes, but may work well while a longer term solution is contemplated. From a philosophical stand point, I strongly believe that consistency is more important than absolute speed. So when a choice must be made, I would always favor slightly reduced but consistent performance over anything that didn’t provide that consistency.
Your comments are always welcome. Please let me know what you think.