绑定变量窥测

绑定变量窥测：

物理优化阶段，查询优化器会窥测绑定变量的值，将它作为字面量来使用。这种方法的问题是它生成的执行计划会依赖第一次生成执行计划时所提供的值。

测试一：
SQL> alter system flush shared_pool;

System altered.

SQL> drop table t;

Table dropped.

SQL> CREATE TABLE t
 2 AS
 3 SELECT rownum AS id, rpad('*',100,'*') AS pad
 4 FROM dual
 5 CONNECT BY level <= 1000;

Table created.

SQL> ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);

Table altered.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> variable num number
SQL> execute :num := 1000

PL/SQL procedure successfully completed.

SQL> set linesize 100 pagesize 100
SQL> select count(pad) from t where id< :num;

COUNT(PAD)
----------
 999

SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID bdkhvu61kj1j2, child number 0
-------------------------------------
select count(pad) from t where id< :num

Plan hash value: 2966233522

-------------------------------------------------------------------------------------
| Id | Operation        | Name  | Starts | E-Rows  | A-Rows | A-Time     | Buffers |
-------------------------------------------------------------------------------------
| 1  | SORT AGGREGATE   |       |       1|       1 |       1|00:00:00.01 |      19 |
|* 2 | TABLE ACCESS FULL|T      |       1|     999 |    999 |00:00:00.01 |      19 |
-------------------------------------------------------------------------------------

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

2 - filter("ID"<:num>

18 rows selected.

SQL> execute :num := 10;

PL/SQL procedure successfully completed.

SQL> select count(pad) from t where id< :num;

COUNT(PAD)
----------
 9

SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID bdkhvu61kj1j2, child number 0
-------------------------------------
select count(pad) from t where id< :num

Plan hash value: 2966233522

-------------------------------------------------------------------------------------
| Id | Operation         | Name | Starts | E-Rows | A-Rows | A-Time     | Buffers |
-------------------------------------------------------------------------------------
| 1  | SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |      19 |
|* 2 | TABLE ACCESS FULL |T     |      1 |    999 |      9 |00:00:00.01 |      19 |
-------------------------------------------------------------------------------------

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

2 - filter("ID"<:num>

18 rows selected.
由测试一可以看出，由于num首先赋值为1000，查询优化器就会选择一个基于全表扫描的一个执行计划，而另一个查询将num赋值为10，虽然实际返回条数是10，仍然采用全表扫描这个执行计划并且预计会预计会返回的条数仍然是999。

测试二：清空内存再来一次，这次先传10再传1000
SQL> alter system flush shared_pool;

System altered.

SQL> execute :num := 10;

PL/SQL procedure successfully completed.

SQL> select count(pad) from t where id< :num;

COUNT(PAD)
----------
 9

SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID bdkhvu61kj1j2, child number 0
-------------------------------------
select count(pad) from t where id< :num

Plan hash value: 4270555908

-----------------------------------------------------------------------------------------------
| Id | Operation                     | Name | Starts | E-Rows | A-Rows | A-Time     | Buffers |
-----------------------------------------------------------------------------------------------
| 1  | SORT AGGREGATE                |      |      1 |      1 |      1 |00:00:00.01 |       3 |
| 2  | TABLE ACCESS BY INDEX ROWID   | T    |      1 |      9 |      9 |00:00:00.01 |       3 |
|* 3 | INDEX RANGE SCAN              | T_PK |      1 |      9 |      9 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------

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

3 - access("ID"<:num>

19 rows selected.

SQL> execute :num := 1000

PL/SQL procedure successfully completed.

SQL> select count(pad) from t where id< :num;

COUNT(PAD)
----------
 999

SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID bdkhvu61kj1j2, child number 0
-------------------------------------
select count(pad) from t where id< :num

Plan hash value: 4270555908

-----------------------------------------------------------------------------------------------
| Id | Operation                   | Name | Starts | E-Rows | A-Rows | A-Time     | Buffers |
-----------------------------------------------------------------------------------------------
| 1  | SORT AGGREGATE              |      |      1 |      1 |      1 |00:00:00.01 |      19 |
| 2  | TABLE ACCESS BY INDEX ROWID | T    |      1 |      9 |    999 |00:00:00.01 |      19 |
|* 3 | INDEX RANGE SCAN            | T_PK |      1 |      9 |    999 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------

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

3 - access("ID"<:num>

只要游标还保存在库缓存中就可以被共享，被重用。不管与它相关的执行计划效率如何。这其实也就可以说明当采用绑定变量是把双刃剑，既有可以共享sql的优点，也有执行计划不准确的缺点。

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24496749/viewspace-723230/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24496749/viewspace-723230/