玉面飞龙的BLOG

Bind variables are good for OLTP systems, because they maximize sharable SQL and minimize CPU usage and latch contention during optimization (see Tom Kyte’s Expert Oracle Database Architecture, Apress, September 2005). But we have just seen that bind variables make a complete nonsense of cardinality calculations. So 9iintroduced bind variable peeking to address this problem.
The first time a piece of SQL is optimized, the optimizer (usually) checks the actual values of any incoming bind variables, and uses those values to do the optimizer calculations, which means the optimizer has a chance of picking the best plan for that first execution.
But on every subsequent occasion that a parse call is issued against that statement, and the text is found to be sharable, the same execution plan will generally be used regardless of any change in the value of the bind variables. (There is an exception relating to large variations in the lengths the character bind variables.)
In an OLTP system, this is likely to be a good thing, as OLTP activity tends to result in the same high precision statements being repeated thousands of times per day, doing the same amount of work each time.
But there are cases, even in an OLTP system, when this can cause problems even table don’t have histograms.

SQL> show parameters optim
NAME                          VALUE
—————————– —–
optimizer_mode         CHOOSE
optimizer_features_enable     9.2.0
_optim_peek_user_binds          TRUE

Prepare a table and its columns value is distributed evenly.

SQL> create table t  as select rownum as num ,object_name from dba_objects;
Table created.
SQL> create index tidx on t (num);
Index created.
SQL> analyze table t compute statistics;
Table analyzed.

Check CBO behavior while query using “>” “<” etc

SQL> alter session set sql_trace=true;
Session altered.

SQL> exec :vb:=100;
PL/SQL procedure successfully completed.
SQL> select count(*) from t where num>:vb and object_name !=’T';
COUNT(*)
———-
18195

Most of num is qulified with > 100 , so FTS is expected.

SQL> exec :vb:=100000000;
PL/SQL procedure successfully completed.
SQL> select count(*) from t where num>:vb and object_name !=’T';
COUNT(*)
———-
0

less value is satisfied with > 100000000, so Index Range Scan is expected. But bind peeking causes using FTS.
SQL> select  /*+another*/  count(*) from t where num>:vb and object_name !=’T';
COUNT(*)
———-
0

Comments makes SQL hard parsed, and less value is satisfied with > 100000000 , CBO choose Index Range Scan .

SQL> exec :vb:=100;
PL/SQL procedure successfully completed.
SQL> select  /*+another*/  count(*) from t where num>:vb and object_name !=’T';
COUNT(*)
———-
18195

FTS is expected. But bind peeking causes using Index Range Scan.

SQL> alter session set sql_trace=false;
Session altered.

TRACE-phxdb025$> tkprof  trace_ora_20593.trc  trace_ora_20593.trc.txt aggregate=no
TKPROF: Release 9.2.0.5.0 - Production on Sun Feb 25 19:09:19 2007
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

******************************************
select count(*)
from
t where num>:vb and object_name !=:”SYS_B_0″

Misses in library cache during parse: 1
Rows     Row Source Operation
——-  —————————–
1  SORT AGGREGATE
18195   TABLE ACCESS FULL T

****************************************
BEGIN :vb:=100000000; END;
****************************************
select count(*)
from
t where num>:vb and object_name !=:”SYS_B_0″

Misses in library cache during parse: 0
Rows     Row Source Operation
——-  ————————————-
1  SORT AGGREGATE
0   TABLE ACCESS FULL T

**********************************************

select  /*+another*/  count(*)
from
t where num>:vb and object_name !=:”SYS_B_0″

Misses in library cache during parse: 1
Rows     Row Source Operation
——-  ———————————–
1  SORT AGGREGATE
0   TABLE ACCESS BY INDEX ROWID T
0    INDEX RANGE SCAN TIDX (object id 41678)

**********************************************
BEGIN :vb:=100; END;
**********************************************

select  /*+another*/  count(*)
from
t where num>:vb and object_name !=:”SYS_B_0″

Misses in library cache during parse: 0
Rows     Row Source Operation
——-  ——————————–
1  SORT AGGREGATE
18195   TABLE ACCESS BY INDEX ROWID T
18196    INDEX RANGE SCAN TIDX (object id 41678)

************************************

10046 trace show exactly executions plan.

The case is caused by unusual bind variable. If  query is executed thousands of times,an OLTP, and first hard parse takes an unusual bind variable, case will be more worse. Another hard parse usually would fix the urgent prolbem.

Usually long solutions would be add hints,or stored outlines,or split SQL in 2 SQL (/*FTS*/&& /*IRS*/) if program is wiser or NOT use bind variable in typical OLAP.Or change parameter to disable bind peeking (Causion!Need TEST!).

Another case is here.