10g 使用bind变量时候PEEKING 会造成执行计划错误,11G进行了改进,但或多或少会造成hard parse,两方面的代价要具体衡量,当然正确的执行计划是更重要的.

先看下 10G 情况

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

peeking 受到这几个隐藏参数影响
SQL> SQL> set linesize 132
column name format a30
column value format a25
select
x.ksppinm name,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj,x. KSPPDESC
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id = userenv('Instance') and
y.inst_id = userenv('Instance') and
x.indx = y.indx and
x.ksppinm like '%peek%'
order by
translate(x.ksppinm, ' _', ' ')
/

SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17
NAME                           VALUE                     ISDEFAULT ISMOD      ISADJ
------------------------------ ------------------------- --------- ---------- -----
KSPPDESC
------------------------------------------------------------------------------------------------------------------------------------
_optim_peek_user_binds         TRUE                      TRUE      FALSE      FALSE
enable peeking of user binds

_px_bind_peek_sharing TRUE TRUE FALSE FALSE
enables sharing of px cursors that were built using bind peeking

_xpl_peeked_binds_log_size 8192 TRUE FALSE FALSE
maximum bytes for logging peeked bind values for V$SQL_PLAN (0 = OFF)

SQL> create table t1 (a int,b int);

Table created.

SQL> declare
2 begin
3 for i in 1..10000 loop
4 insert into t1 values(i,i+1);
5 end loop;
6 commit;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.gather_table_stats('SYS','T1');

PL/SQL procedure successfully completed.

SQL> select count(*) from t1;

COUNT(*)
----------
10000

SQL> create index t1_ind on t1(a);

Index created.

SQL> set autotrace off
SQL> create index t1_ind on t1(a);

Index created.
SQL> set autotrace trace explain
SQL> select * from t1 where a>1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 9999 | 69993 |     6   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1   | 9999 | 69993 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("A">1)

SQL> select * from t1 where a>9000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2059591622

--------------------------------------------------------------------------------------
| Id | Operation                   | Name   | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        | 1000 | 7000 |     6   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| T1     | 1000 | 7000 |     6   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | T1_IND | 1000 |       |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

2 - access("A">9000)

可以看到上面采用字面值的情况，下面使用BIND 变量

QL> variable a number
SQL> execute :a:=1

PL/SQL procedure successfully completed.

SQL> select * from t1 where a>:a;

Execution Plan
----------------------------------------------------------
Plan hash value: 2059591622

--------------------------------------------------------------------------------------
| Id | Operation                   | Name   | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |   500 | 3500 |     3   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| T1     |   500 | 3500 |     3   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | T1_IND |    90 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

2 - access("A">TO_NUMBER(:A))

问题来了~~~按照PEEKING 的原则至少oracle应该确认 BIND 值为1 走FTS ，但显示为INDEX RANGE SCAN

现在使用V$sql_plan来查看执行计划

SQL> alter system flush shared_pool;

System altered.

SQL> variable a number
SQL> execute :a:=1

PL/SQL procedure successfully completed.
select * from t1 where a>:a

SQL> col sql_text format a40
SQL> select hash_value ,sql_text from v$sqlarea where sql_text like '%select * from t1 where a>%';

HASH_VALUE SQL_TEXT
---------- ----------------------------------------
3711359813 select * from t1 where a>:a
2165387111 select hash_value ,sql_text from v$sqlar
ea where sql_text like '%select * from t
1 where a>

SQL> select operation,options,object_name,cardinality from v$sql_plan where hash_value='3711359813';

OPERATION OPTIONS
------------------------------ ------------------------------
OBJECT_NAME CARDINALITY
------------------------------ -----------
SELECT STATEMENT

TABLE ACCESS FULL
T1 9999

可以看到 set autotrace 不准确 ~~，具体应该依赖于v$sql_plan，下面继续实验
execute :a:=9000
select * from t1 where a>:a
;

SQL> select hash_value ,sql_text from v$sqlarea where sql_text like '%select * from t1 where a>%';

HASH_VALUE SQL_TEXT
---------- ----------------------------------------
3711359813 select * from t1 where a>:a
2165387111 select hash_value ,sql_text from v$sqlar
ea where sql_text like '%select * from t
1 where a>%'

SQL> select operation,
options,object_name,cardinality from v$sql_plan where hash_value='3711359813';

OPERATION OPTIONS
------------------------------ ------------------------------
OBJECT_NAME CARDINALITY
------------------------------ -----------
SELECT STATEMENT

TABLE ACCESS FULL
T1 9999

可以看到oracle peeking了一次，然后按第一次FTS 方式共享CURSOR ,共享了FTS 的 EXECUTE PLAN

看下11G 情况
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Productio
NLSRTL Version 11.1.0.6.0 - Production

SQL> create table t1 (a int, b int);

Table created.

SQL> declare
2 begin
3 for i in 1..10000 loop
4 insert into t1 values(i,i+1);
5 end loop;
6 commit;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> create index t1_ind on t1(a);

Index created.

SQL> execute dbms_stats.gather_table_stats('SYS','T1');

SQL> variable a number
SQL> execute :a:=1

PL/SQL procedure successfully completed.

select * from t1 where a>:a;

SQL> select hash_value ,sql_text from v$sqlarea where sql_text like '%select * from t1 where a>%';

HASH_VALUE SQL_TEXT
---------- ----------------------------------------
2165387111 select hash_value ,sql_text from v$sqlar
ea where sql_text like '%select * from t
1 where a>%'

2263578425 select * from t1 where a>:a

SQL> select operation,
2 options,object_name,cardinality from v$sql_plan where hash_value='2263578425';

OPERATION OPTIONS
------------------------------ ------------------------------
OBJECT_NAME CARDINALITY
------------------------------ -----------
SELECT STATEMENT

TABLE ACCESS FULL
T1 9999

SQL> execute :a:=9000

PL/SQL procedure successfully completed.

SQL> select * from t1 where a>:a;

SQL> select hash_value ,sql_text from v$sqlarea where sql_text like '%select * from t1 where a>%';

HASH_VALUE SQL_TEXT
---------- ----------------------------------------
2165387111 select hash_value ,sql_text from v$sqlar
ea where sql_text like '%select * from t
1 where a>%'

2263578425 select * from t1 where a>:a

SQL> select operation,
2 options,object_name,cardinality from v$sql_plan where hash_value='2263578425';

OPERATION OPTIONS
------------------------------ ------------------------------
OBJECT_NAME CARDINALITY
------------------------------ -----------
SELECT STATEMENT

TABLE ACCESS FULL
T1 9999

SQL> execute :a:=9999

PL/SQL procedure successfully completed.

SQL> select * from t1 where a>:a;

A B
---------- ----------
10000 10001

SQL> select hash_value ,sql_text from v$sqlarea where sql_text like '%select * from t1 where a>%';

HASH_VALUE SQL_TEXT
---------- ----------------------------------------
2165387111 select hash_value ,sql_text from v$sqlar
ea where sql_text like '%select * from t
1 where a>%'

2263578425 select * from t1 where a>:a

SQL> select operation,
2 options,object_name,cardinality from v$sql_plan where hash_value='2263578425';

OPERATION OPTIONS
------------------------------ ------------------------------
OBJECT_NAME CARDINALITY
------------------------------ -----------
SELECT STATEMENT

TABLE ACCESS BY INDEX ROWID
T1 1

INDEX RANGE SCAN
T1_IND 1

OPERATION OPTIONS
------------------------------ ------------------------------
OBJECT_NAME CARDINALITY
------------------------------ -----------
SELECT STATEMENT

TABLE ACCESS FULL
T1 9999

select * from t1 where a>9000; (9000的时候也应该走INDEX )

SQL> select hash_value ,sql_text from v$sqlarea where sql_text like '%select * from t1 where a>9000%';

HASH_VALUE SQL_TEXT
---------- ----------------------------------------
705708754 select * from t1 where a>9000
1110618947 select hash_value ,sql_text from v$sqlar
ea where sql_text like '%select * from t
1 where a>9000%'

SQL> select operation,
2 options,object_name,cardinality from v$sql_plan where hash_value='705708754';

OPERATION OPTIONS
------------------------------ ------------------------------
OBJECT_NAME CARDINALITY
------------------------------ -----------
SELECT STATEMENT

TABLE ACCESS BY INDEX ROWID
T1 1000

INDEX RANGE SCAN
T1_IND 1000

SQL> select sql_id,child_number,plan_hash_value from v$sql where hash_value='2263578425'
2 /

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE
------------- ------------ ---------------
5mu9n4f3fqxtt            0      3617692013
5mu9n4f3fqxtt            1      2059591622

产生了子游标，且产生了不同的执行计划(plan_hash_value判断多了一个INDEX SCAN的plan)

SQL> select child_number,is_bind_sensitive,is_bind_aware,is_shareable from v$sql where sql_id='5mu9n4f3fqxtt' order by child_number;

CHILD_NUMBER I I I
------------ - - -
0 Y N Y
1 Y Y Y

SQL> select child_number,peeked,executions,rows_processed,buffer_gets from v$sql_cs_statistics where sql_id='5mu9n4f3fqxtt' order by child_number;

CHILD_NUMBER P EXECUTIONS ROWS_PROCESSED BUFFER_GETS
------------ - ---------- -------------- -----------
0 Y 1 9999 722
1 Y 1 1 3

SQL> select child_number,predicate,low,high from v$sql_cs_selectivity where sql_id='5mu9n4f3fqxtt' order by child_number;

CHILD_NUMBER PREDICATE LOW HIGH
------------ ---------------------------------------- ---------- ----------
1 >A 0.000090 0.000110

execute :a:=1000
select * from t1 where a>:a

这个计划走的是FTS 又产生一个version_count，

SQL> select child_number,peeked,executions,rows_processed,buffer_gets from v$sql_cs_statistics where sql_id='5mu9n4f3fqxtt' order by child_number;

CHILD_NUMBER P EXECUTIONS ROWS_PROCESSED BUFFER_GETS
------------ - ---------- -------------- -----------
           0 Y          1           9999         722
           1 Y          1              1           3
           2 Y          1           9000         622

SQL> select child_number,is_bind_sensitive,is_bind_aware,is_shareable from v$sql where sql_id='5mu9n4f3fqxtt' order by child_number;

CHILD_NUMBER I I I
------------ - - -
           0 Y N N
           1 Y Y Y
           2 Y Y Y

SQL> select child_number,predicate,low,high from v$sql_cs_selectivity where sql_id='5mu9n4f3fqxtt' order by child_number;

CHILD_NUMBER PREDICATE                                LOW        HIGH
------------ ---------------------------------------- ---------- ----------
           1 >A                                       0.000090   0.000110
           2 >A                                       0.810081   0.990099

SQL> select sql_id,child_number,plan_hash_value from v$sql where hash_value='2263578425'
2 ;

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE
------------- ------------ ---------------
5mu9n4f3fqxtt            0      3617692013
5mu9n4f3fqxtt            1      2059591622
5mu9n4f3fqxtt            2      3617692013

SQL> execute :a:=2000

PL/SQL procedure successfully completed.

select * from t1 where a>:a

SQL> select sql_id,child_number,plan_hash_value from v$sql where hash_value='2263578425';

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE
------------- ------------ ---------------
5mu9n4f3fqxtt            0      3617692013
5mu9n4f3fqxtt            1      2059591622
5mu9n4f3fqxtt            2      3617692013
5mu9n4f3fqxtt            3      3617692013

SQL> select child_number,predicate,low,high from v$sql_cs_selectivity where sql_id='5mu9n4f3fqxtt' order by child_number;

CHILD_NUMBER PREDICATE                                LOW        HIGH
------------ ---------------------------------------- ---------- ----------
           1 >A                                       0.000090   0.000110
           2 >A                                       0.810081   0.990099
           3 >A                                       0.720072   0.990099

SQL> select child_number,peeked,executions,rows_processed,buffer_gets from v$sql_cs_statistics where sql_id='5mu9n4f3fqxtt' order by child_number;

CHILD_NUMBER P EXECUTIONS ROWS_PROCESSED BUFFER_GETS
------------ - ---------- -------------- -----------
           0 Y          1           9999         722
           1 Y          1              1           3
           2 Y          1           9000         622
           3 Y          1           8000         556

SQL> select child_number,is_bind_sensitive,is_bind_aware,is_shareable from v$sql where sql_id='5mu9n4f3fqxtt' order by child_number;

CHILD_NUMBER I I I
------------ - - -
           0 Y N N
           1 Y Y Y
           2 Y Y N
           3 Y Y Y

SQL> execute :a:=1500

PL/SQL procedure successfully completed.

select * from t1 where a>:a;
8500 rows selected.