23 Responses to “Bind Variable Peeking with no Histograms”  

1. 1 Jean-Pol Feb 5th, 2007 at 6:12 am

   We have seen performances problems on some queries in our application when migrating the JDBC driver from 9.2.0.7 to 10.2.0.2 (both connected to the same instance of Oracle 9.2.0.7; the upgrade is required because we are going to use JDK 1.5 and the jdbc driver 9.2.0.7 is not certified for this version of the JDK).
   I’m already working on these problems for one week now. I have tried many things, until I’ve discovered the 9i driver reacts well only when using bind variables while the 10g driver reacts bad either using bind variables or not.
   Searching on the web for performance problems with bind variables, I’ve finally discovered this article (coming from http://oracleandy.blogspot.com/2006/05/bind-variable-peeking.html).
   Then I have tried setting _optim_peek_user_binds to false with the 10g driver and now we are getting similar performance results as with the 9i driver. We can’t really explain why it’s like that just by changing the version of the driver, but to us it seems the 9i driver reacts like if this parameter was disabled by default, while the 10g driver reacts like if it was enabled by default.

   Anyway, thank you very much for this article. We have updated our SR in metalink to tell Oracle about that. We have directed them to this article. We hope we will get an explanation soon.

2. 2 Alex Gorbachev Feb 5th, 2007 at 9:16 am

   Jean-Pol, thanks for your feedback.
   This post is not much related to you problem I believe but I glad that as least part of it helped.
   If your performance issues are related to changes in execution plans (perhaps, due to bind variable peeking) than I would suggest to run the problematic statement with trace event 10053 and see there why optimizer does it and particularly pay attention to any differences in the parameters.

3. 3 Jean-Pol Feb 8th, 2007 at 11:53 am

   I was saying this in my previous post:
   “to us it seems the 9i driver reacts like if this parameter was disabled by default, while the 10g driver reacts like if it was enabled by default.”

   That has been confirmed by our Oracle support. Moreover, it just depends on the version of the driver, no matter what’s the version of the database.

   Oracle told us we can safely use _optim_peek_user_binds = FALSE in our production system without any danger or obscure side effect.

4. 4 Alex Gorbachev Feb 8th, 2007 at 6:55 pm

   That has been confirmed by our Oracle support. Moreover, it just depends on the version of the driver, no matter what’s the version of the database.

   This is weird. Thanks for this info.

5. 5 David Perez Apr 1st, 2007 at 4:32 am

   Hi Alex

   Alex, your example uses partitions, does this only happens with partitioned tables?

   I have seen bind peeking happens with no histograms but I cannot find a explanation (9.2.0.7). The queries which I have seen this happen are usually not a plain query involving tables, they always involves JOIN.

   Cheers

   David

6. 6 Alex Gorbachev Apr 1st, 2007 at 8:44 am

   David,

   The purpose of this example was to demonstrate exactly that bind variable peaking happens regardless presence of histograms. The most common impact of bind variable peaking is caused by histograms. I showed another possible impact coming from partitioning. It’s absolutely possible that Oracle CBO is able to change join cardinality evaluation using bind variable peaking. Perhaps, query rewrite happens and predicate is actually applied to another table that happens to have histograms. 10053 trace should uncover more.

   Thanks for stopping by.

   Alex

7. 7 Jonas Rosenthal May 14th, 2007 at 6:23 pm

   Hi Alex,

   I Found your test most thoughtful. Just to add in with one simple observation: I went back and checked the manual for 10gR12 and it doesn’t differentiate bind peeking with histograms or without histograms. Just curious, is there another Oracle document that contratradicted this?

   From Chapter 13 of the Performance tuning Guide:
   “13.4.1.2 Peeking of User-Defined Bind Variables
   The query optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This feature lets the optimizer determine the selectivity of any WHERE clause condition, as well as if literals have been used instead of bind variables. On subsequent invocations of the cursor, no peeking takes place, and the cursor is shared, based on the standard cursor-sharing criteria, even if subsequent invocations use different bind values.

   When bind variables are used in a statement, it is assumed that cursor sharing is intended and that different invocations are supposed to use the same execution plan. If different invocations of the cursor would significantly benefit from different execution plans, then bind variables may have been used inappropriately in the SQL statement. Bind peeking works for a specific set of clients, not all clients.”

   Anyway, since you flushed the shared pool, I would expect it to peek again. Also, since partition elimination is involved for the partcol key, the amount of rows in each partition makes it likely that with only a few rows in the second example that it would full scan the partition. In other words, everything I saw was what I expected. In any case, I never knew about this bind parameter. Thanks for sharing. Please let me know if I’m missing anything here.

8. 8 Alex Gorbachev May 14th, 2007 at 8:56 pm

   Jonas,
   Shared pool flush is there exactly for that - to make sure we get a hard parse again. The case I demonstrated is for illustration only.

   Imagine the first example in the “Peeking” sessions:
   If the first statement (i.e. on a hard parse) uses binds targeting small partition - CBO produces execution plan with full partition scan. All consequent executions reusing that execution plan after soft parse will perform full partition scan that is a killer for large partitions (majority).

9. 9 Paulk Oct 22nd, 2007 at 10:44 am

   Nice demonstration. It’s good to be reminded that Oracle peeks on a hard parse and that even without histograms, different inputs can imply different plans.

10. 10 Alex Gorbachev Oct 28th, 2007 at 12:06 am

    Thanks Paulk. Even simpler demonstration would be with peeking on range scans and different ranges depending on maxval and minval but that was my original real-life case.

11. 11 Vyacheslav Rasskazov Jan 15th, 2008 at 3:36 am

    Hi Alex
    You said that “Oracle histograms are often treated as devil of CBO as they cause bind variable peeking”.
    But I don’t see any difference in CBO behavior depended of histograms existence (at least in 10g) when bind variables used.
    Oracle performs bind variable peeking first time and used obtained plan further, regardless of histograms availability.
    It’s my testcase here

    rasskazov@BISDB> create table tst as select mod(rownum,2) num, ‘xx’ name from dual connect by level insert into tst (num, name) values(5, ‘zz’);

    1 row created.

    rasskazov@BISDB> commit;

    Commit complete.

    rasskazov@BISDB> create index idx_tst on tst (num);

    Index created.

    rasskazov@BISDB> BEGIN
    2 SYS.DBMS_STATS.GATHER_TABLE_STATS (
    3 OwnName => user
    4 ,TabName => ‘TST’
    5 ,Estimate_Percent => NULL
    6 ,Method_Opt => ‘FOR COLUMNS SIZE 3 NUM’
    7 ,Cascade => FALSE
    8 ,No_Invalidate => FALSE);
    9 END;
    10 /

    PL/SQL procedure successfully completed.

    rasskazov@BISDB> set autotrace traceonly
    rasskazov@BISDB> select * from tst where num = 1;

    5000 rows selected.

    Execution Plan
    ———————————————————-
    Plan hash value: 2553322291

    ————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————–
    | 0 | SELECT STATEMENT | | 5000 | 25000 | 3 (0)| 00:00:01 |
    |* 1 | TABLE ACCESS FULL| TST | 5000 | 25000 | 3 (0)| 00:00:01 |
    ————————————————————————–

    Predicate Information (identified by operation id):
    —————————————————

    1 - filter(”NUM”=1)

    Statistics
    ———————————————————-
    1 recursive calls
    0 db block gets
    357 consistent gets
    0 physical reads
    0 redo size
    37556 bytes sent via SQL*Net to client
    2574 bytes received via SQL*Net from client
    335 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    5000 rows processed

    rasskazov@BISDB> select * from tst where num = 5;

    Execution Plan
    ———————————————————-
    Plan hash value: 716636544

    —————————————————————————————
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    —————————————————————————————
    | 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
    | 1 | TABLE ACCESS BY INDEX ROWID| TST | 1 | 5 | 2 (0)| 00:00:01 |
    |* 2 | INDEX RANGE SCAN | IDX_TST | 1 | | 1 (0)| 00:00:01 |
    —————————————————————————————

    Predicate Information (identified by operation id):
    —————————————————

    2 - access(”NUM”=5)

    Statistics
    ———————————————————-
    1 recursive calls
    0 db block gets
    3 consistent gets
    30 physical reads
    0 redo size
    253 bytes sent via SQL*Net to client
    243 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    1 rows processed

    rasskazov@BISDB> var zz number;
    rasskazov@BISDB> exec :zz := 1;

    PL/SQL procedure successfully completed.

    rasskazov@BISDB> select * from tst where num = :zz;

    5000 rows selected.

    Execution Plan
    ———————————————————-
    Plan hash value: 2553322291

    ————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————–
    | 0 | SELECT STATEMENT | | 3333 | 16665 | 3 (0)| 00:00:01 |
    |* 1 | TABLE ACCESS FULL| TST | 3333 | 16665 | 3 (0)| 00:00:01 |
    ————————————————————————–

    Predicate Information (identified by operation id):
    —————————————————

    1 - filter(”NUM”=TO_NUMBER(:ZZ))

    Statistics
    ———————————————————-
    1 recursive calls
    0 db block gets
    357 consistent gets
    0 physical reads
    0 redo size
    37892 bytes sent via SQL*Net to client
    2574 bytes received via SQL*Net from client
    335 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    5000 rows processed

    rasskazov@BISDB> exec :zz := 5;

    PL/SQL procedure successfully completed.

    rasskazov@BISDB> select * from tst where num = :zz;

    Execution Plan
    ———————————————————-
    Plan hash value: 2553322291

    ————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————–
    | 0 | SELECT STATEMENT | | 3333 | 16665 | 3 (0)| 00:00:01 |
    |* 1 | TABLE ACCESS FULL| TST | 3333 | 16665 | 3 (0)| 00:00:01 |
    ————————————————————————–

    Predicate Information (identified by operation id):
    —————————————————

    1 - filter(”NUM”=TO_NUMBER(:ZZ))

    Statistics
    ———————————————————-
    0 recursive calls
    0 db block gets
    23 consistent gets
    0 physical reads
    0 redo size
    253 bytes sent via SQL*Net to client
    243 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    1 rows processed

    rasskazov@BISDB> spool off

12. 12 Alex Gorbachev Jan 30th, 2008 at 11:40 pm

    Vyacheslav,

    First of all, thanks for reading the blog and commenting.

    What you said is absolutely correct and if you re-read the post, you’d see — it’s what I tired to show with my example. Statement “Oracle histograms are often treated as devil of CBO as they cause bind variable peeking” is basically a myth I wanted to prove wrong. Histograms don’t cause bind variable peaking. They increase probability of unstable execution plans using bind variables.

    Now, without looking at the details on your test case, you might be interested in looking at autotrace specific that causes it to provide execution plan that isn’t actually used during execution.

    Also, I’m not sure how you wanted to show it in your example since it only showed case with histograms (‘FOR COLUMNS SIZE 3 NUM). Did I miss something?

    Again, thanks for visiting my blog.

13. 13 Vyacheslav Rasskazov Feb 4th, 2008 at 2:05 am

    Alex,
    In my example I showed exactly what you said. Two test cases: first with histograms and literals, and second with histograms and bind variables. Appropriate execution plan for both queries in first test case and only for one in second.
    Thank you for link to Jonathan Lewis article.

14. 14 Milen Kulev Feb 20th, 2008 at 9:27 am

    Alex,
    very useful and refreshing post.
    The problem is that due to a bug 5082178 , bind variables are peekded, even if
    “_optim_peek_user_binds”=FALSE.
    I have had enormous problems with plan stability (on 10.2.0.2 ) with some SQLs,
    and “_optim_peek_user_binds” was set to FALSE.
    So, the problem should have been fixed in 10.2.0.4 , accourt to Oracle Corp.
    (see http://www.dbatools.net/doc/bug10204.html). But first I want to see and then (eventually) believe it .

    Best Regards. Milen

15. 15 Alex Gorbachev Feb 20th, 2008 at 9:37 am

    Thanks for this Milen. The bug itself is not public but Metalink Note 5082178.8 says that In some situations bind peeking can occur when it should not.

    Do you have more details about some situations?

16. 16 Milen Kulev Feb 20th, 2008 at 9:43 am

    Hello agian Alex,
    whether a bind variable is peeked depends not only the presence of histograms, bit also on the value of the parameter CURSOR_SHARING.
    CBO decides to peek (provided that “_optim_peek_user_binds”=TRUE & in absense of bugs -> see my latest post ) the deepending on whether the bind variable is considered “unsafe”.
    A very good read are ML 296377.1 and 377847.1.
    According to ML 296377.1 the “ultimate” proof (but not the reason why ! ;( ) that a bind variable is considered as “unsafe” is the oacfl2 attribute in a 10046 trace file. Short excerpt from the first ML note:

    —————————————————
    It is also possible to tell from 10046 trace (level 4/12 - BINDS) if a bind is considered to be unsafe

    The flag oacfl2 in 9i and fl2 in 10g will show if a variable is unsafe.

    BINDS #2:
    bind 0: dty=2 mxl=22(04) mal=00 scl=00 pre=00 oacflg=10 oacfl2=500 size=24
    offset=0
    bfp=1036d6408 bln=22 avl=04 flg=09
    value=16064
    bind 1: dty=2 mxl=22(04) mal=00 scl=00 pre=00 oacflg=10 oacfl2=500 size=24
    offset=0
    bfp=1036d4340 bln=22 avl=04 flg=09

    If you note oacfl2=500
    #define UACFBLTR 0×00000100 /* Bind was generated by LiTeRal replacement */
    #define UACFUNSL 0×00000200 /* UNSafe Literal */
    #define UACFNDTL 0×00000400 /* Non-DaTa LiteRal */

    —————————————————

    HTH. Milen

17. 17 Alex Gorbachev Feb 21st, 2008 at 10:11 pm

    Thanks for the references, Milen.
    In my experience _optim_peek_user_binds parameter has helped pretty much every time and especially in cases with CURSOR_SHARING=SIMILAR.
    There is another interesting Metalink Note () that references couple other bugs causing execution plan instability in the section “CONCLUSION” — bugs 4567767 and 5364143.

18. 18 David Jones May 12th, 2008 at 5:05 am

    Thanks to all the contributors to this thread, which has been very useful in confirming and addressing our own similar problems.

    I’m intrigued by the extract of Oracle documentation posted by Jonas:

    >>
    When bind variables are used in a statement, it is assumed that cursor sharing is intended and that different invocations are supposed to use the same execution plan. If different invocations of the cursor would significantly benefit from different execution plans, then bind variables may have been used inappropriately in the SQL statement. Bind peeking works for a specific set of clients, not all clients.”
    >>

    It seems to me Oracle have painted themselves into a logical corner: In a situation where it is not the case that “different invocations of the cursor would significantly benefit from different execution plans”, then there is no point in peeking the bind variable to determine the best path. So, if the one time that peeking the variable would be useful is the scenario where “bind variables may have been used inappropriately in the SQL statement”, why bother to implement bind variable peeking at all, ever?!

    Did I miss something?

    David

19. 19 Alex Gorbachev May 13th, 2008 at 1:24 pm

    There are cases when you want to use bind variables (majority I’d say) and when you want to use literals (rather exceptional). Unfortunately, most developers don’t bother and either used binds everywhere or nowhere (there are good developers as well that do it properly - I’m luck to work with some of those).

    BVP is a workaround for code where BV are used everywhere even where a literal would be more appropriate.

    cursor_sharing=force/similar is workaround for the opposite - when binds are not used at all.

    I think Oracle did good job disabling cursor sharing workaround by default (i.e. cursor_sharing=exact) but they did a mistake enabling BVP by default and, actually, not even exposing the underscore parameter to disable it.

    Btw, when those two features are combined — you have the more “intriguing” effects (and bugs).

20. 20 Polarski Bernard May 30th, 2008 at 2:08 am

    Why is it that cost of full tablescan is different between the 2 peeking ?

    peek=1 :

    Access Path: TableScan
    Cost: 86.84 Resp: 86.84 Degree: 0
    Cost_io: 80.00 Cost_cpu: 3117218

    peek=9000001

    Cost: 86.84 Resp: 86.84 Degree: 0
    Cost_io: 80.00 Cost_cpu: 3117218
    Resp_io: 80.00 Resp_cpu: 3117218

21. 21 Polarski Bernard May 30th, 2008 at 2:12 am

    sorry I press too fast, the facts are :

    peek=1 :
    Access Path: TableScan
    Cost: 86.84 Resp: 86.84 Degree: 0
    Cost_io: 80.00 Cost_cpu: 3117218
    Resp_io: 80.00 Resp_cpu: 3117218

    peek=9000001

    Access Path: TableScan
    Cost: 2.02 Resp: 2.02 Degree: 0
    Cost_io: 2.00 Cost_cpu: 7851
    Resp_io: 2.00 Resp_cpu: 7851

1. 1 Pythian Group Blog » The First Miracle Open World: Day 2 Pingback on Oct 1st, 2007 at 2:11 pm
2. 2 Bind Peeking, Ad Hoc Queries, Stable Performance. On 10G you can only pick any two. Pingback on Mar 12th, 2008 at 8:04 am

Leave a Reply