Oracloid Blog

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.

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.

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.

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.

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

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

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.

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

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.

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

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.

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

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

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

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

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

I don’t understand your example
You are using histograms because you use: method_opt => ‘FOR ALL COLUMNS SIZE 1‘

You are using a histogram with one bucket.

If you don’t want to use histograms why do you include this clause?

I think that you must do the example with histograms disabled. You can’t prove
that peeking with no histograms happens if you are using histograms

@Alex Gorbachev
method_opt => ‘FOR COLUMNS’ will collect statistics without any histograms (dba_tab_histograms will show no rows)

Hi Alex,

I found the “Bind Variable Peeking with no Histograms” interesting.
here is my observation and some test.
I used 10.1 for this test.

create table cst (cno number,status varchar2(3));
–
begin
for i in 1..100000
loop
if mod(i,100000)!=0 then
insert into cst values(i,’OK’);
else
insert into cst values(i,’Can’);
end if;
end loop;
end;
/
–
create index cst_status_idx on cst(status);
–
execute dbms_stats.gahter_table_stats(ownname=>’MDATA’,tabname=>’CST’,cascade=>TRUE,method_opt=>’for all columns size 2′);
–
var st varchar2(3);
execute :st := ‘OK’;
select * from cst where status=:st;

at this point I verified with v$sql and joining v$sql_plan its showing FTS of CST.

Now I executed
execute :st := ‘Can’;
select * from cst where status=:st;

again verified v$sql and V4sql_plan, no new chld generated and execution plan still showing FTS.
at this point of time I started trace of current session
alter session set sql_trace=true;
execute :st := ‘Can’;
select * from cst where status=:st;

——
to my surprise now trace file is showing index scan and V$sql showing a child and v$sql_plan showing index scan for the child query.

Is it a bug in 10.1 that it does not show up correct plan ion v$sql and after setting the trace it is showing correct plan with bind peeking each time query is executed.

————
I repeated this test with 9.2 also
to my surprise in 9i it is behaving correctly with setting the trace . if correct historgram are present then as per the bind variable value query plan is changing (FTS or index range).

Can you please help me understand this behaviour. Is it a bug in 10g (10.1 I have not tested on 10g 10.2 yet).

Best Regards,
Mukesh Sharma

bind peeking happens with and without histogram also till 10g, from 11g onwards with adaptive cursor sharing this problem resolved.