Bind variables 和 bind variable peeking

在我们具体阐述问题之前，先建立一个测试用例：一张表test，列status存在2个值，有数据倾斜，在列status上有索引。
CREATE TABLE test
AS
SELECT ROWNUM id,
       DBMS_RANDOM.STRING('A', 12) name,
       DECODE(MOD(ROWNUM, 500), 0, 'Inactive', 'Active') status
  FROM all_objects
 WHERE ROWNUM <= 50000;


CREATE INDEX test_id_ind ON test(status);
EXEC dbms_stats.gather_table_stats(user, 'test', method_opt => 'for columns status size 254', cascade => true);


SELECT COUNT (*) cnt, status
  FROM test
GROUP BY status
/


       CNT STATUS
---------- ----------------
     14820 Active
        29 Inactive

我们可以很清楚的看到，列status存在两个唯一值，列上有倾斜，大部分的status为Active，只有少量的status为Inactive。
接着我们执行三条语句：
select name from test where id = 1
/                
select name from test where id = 2
/                
select name from test where id = 3
/

每次执行这个语句的时候，ORACLE会将它转换为ASCII然后采用一个HASH算法对这个ASCII进行HASH，然后根据HASH值检查这个SQL是否已经在共享池中，ORACLE检查共享池的时候，会做精准的匹配检查，如果这个语句在共享池中，ORACLE将重用这个SQL的游标（软解析） ，如果这个 语句不在共享池，ORACLE将会做硬解析，硬解析是非常昂贵的操作，因为它会消耗很多的CPU资源，需要获得LATCH和访问、占用一些共享内存结构。可能学过其他一些数据库的同学会问，为什么ORACLE需要一个共享池的组建？为什么ORACLE需要做软解析？而MYSQL,MONGODB等一些数据库都是硬解析。主要的原因还是ORACLE的优化器算法非常复杂，不像MYSQL这些数据库。由于优化器算法过于复杂，导致硬解析要消耗更多的CPU，而像MYSQL这些数据库，优化器相对来说比较弱，硬解析代价不高，也就不需要缓存SQL执行计划，每次都做硬解析也不会产生过高的代价。
我们看下上面执行的3个SQL在共享池里是否被缓存了。

SELECT sql_text, hash_value, executions

   FROM v$sql

  WHERE sql_text LIKE ' select name from test %'

SQL_TEXT                                                     HASH_VALUE EXECUTIONS
------------------------------------------------------------ ---------- ----------
select name from test where id = 1                           2165441778          1
select name from test where id = 3                           3138738168          1
select name from test where id = 2                           2203286382          1


我们可以看到刚才执行的三个SQL，仅仅变量值不一样，就 在共享池里占用了3个条目，每一个SQL的SQL文本经过HASH之后的HASH VALUE 都不一样。为了解决这个问题，避免硬解析，让ORACLE重用存在的解析计划，我们需要使用绑定变量来做到这一点。

可以将：
select name from test where id = 1
写为：
select name from test where id = :a;

经过这样转换之后，ORACLE计算HASH VALUE都是基于 select name from test where id = :a来计算，因此不管:a是何值都会共享之前产生的执行计划。
alter system flush shared_pool;
var a number
exec :a :=1

select name from test where id = :a;


NAME
-------------------------------------------------------
cuzgctDUzWVk


exec :a :=2


PL/SQL procedure successfully completed.


select name from test where id = :a;


NAME
-------------------------------------------------------
EwNouklMnqQt


exec :a :=3


PL/SQL procedure successfully completed.


select name from test where id = :a;


NAME
-------------------------------------------------------
IvwZVtQeHkWB

SELECT sql_text, hash_value, executions

   FROM v$sql

  WHERE sql_text LIKE ' select name from test %'

    
SQL_TEXT                                                     HASH_VALUE EXECUTIONS
------------------------------------------------------------ ---------- ----------
select name from test where id = :a                          3111269909          3

看到执行结果跟之前的差异了吗？
我们执行了相同的SQL三次，但是传入了不同的值，在共享池中只存在一个SQL，这是非常好的，正是我们想要的。通过这种方式，我们节约了CPU资源，节约了LATCH的获取等等。
这样看来绑定变量是好的，但是。。
如果优化器能知道文本变量的值，更容易 产生更加优秀的执行计划，这意味着使用文本变量更容易产生好的执行计划，而非绑定变量。
当你执行一个SQL的时，如果你使用了绑定变量，这将意味着变量的值对优化器是未知的，优化器只能把它当做一个普通值处理。产生的执行计划就可能不够符合值的特征。
例如：
select * from test  where status = ‘Active’
 
和
 
select * from test  where status = ‘Inactive’

当执行status为Active时，应该走全表扫描更快，当执行Inactive时，应该走索引扫描更快。（优化器参考直方图来判断）
但是由于你传入的值只是:a，优化器就不能更加智能 的做出判断。
test@DLSP>explain plan for
  2  select * from test where status = 'Active';

Explained.

test@DLSP>@display


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1357081020


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 14820 |   347K|    17   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST | 14820 |   347K|    17   (0)| 00:00:01 |
--------------------------------------------------------------------------


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


   1 - filter("STATUS"='Active')


13 rows selected.


test@DLSP>explain plan for
  2  select * from test where status = 'Inactive';


Explained.


test@DLSP>@display


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1314416543


-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |    29 |   696 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |    29 |   696 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_ID_IND |    29 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------


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


   2 - access("STATUS"='Inactive')


14 rows selected.
从上面我们看到当我们执行stats='Active'的时候，优化器选择了全表扫描，这是因为优化器参照列status上的直方图后，知道表里大部分数据都是status为Active的，走全表扫描会更快，当执行status='Inactive'的时候，优化器参照列status上的直方图后，知道Inactive的值非常少，因此选择了索引扫描，走索引更快。
通过如下代码检查直方图是否被创建：
set pagesize 120
set linesize 120
 set num 35
column owner        format a10
column table_name   format a12
column column_name  format a12
column aev          format a12
SELECT owner, table_name, column_name, endpoint_number en, endpoint_value ev,
       endpoint_actual_value aev
  FROM dba_tab_histograms
 WHERE table_name = 'TEST'
/


我们看到我们使用了字符常量后，优化器知道如何根据直方图信息生成优秀的执行计划，那么使用绑定变量会变成什么样呢？这个依赖于ORACLE的版本。
在ORACLE 9i/10g版本，ORACLE会在第一次硬解析的时候，窥探绑定变量的值，然后生成执行计划，后面的SQL都会使用这个执行计划来执行。（引发了很多问题）
在ORACLE 11g版本，ORACLE有一个adaptive cursor sharing的特性，根据不同的绑定值可能产生不同的执行计划，可以解决9i/10g的问题。


oracle 9i/10g：
场景1：
var a varchar2(100)
exec :a :='Active'
select * from test where status=:a;
exec :a :='Inactive'
select * from test where status=:a;

场景2（刷新共享池）：
var a varchar2(100)
exec :a :='Inactive'
select * from test where status=:a;
exec :a :='Active'
select * from test where status=:a;

场景一中，由于先执行status为Active的，由于是第一次执行要做硬解析，优化器会窥探到绑定变量的值Active，因此执行计划会是全表扫描，然后执行status为Inactive的SQL，由于之前执行的SQL已经被缓存，因此优化器会重用这个CURSOR，称之为软解析。执行计划依然会采用全表扫描。
场景二中，由于先执行status为Inactive的，由于是第一次执行要硬解析，优化器会窥探到绑定变量的值Inactive，因此执行计划会是索引扫描，然后执行status为Active的SQL，由于之前执行的SQL已经被缓存，因此优化器会重用这个CURSOR，，执行计划会继续使用索引扫描。

用实验证明：
场景一：
var a varchar2(100)
exec :a :='Active'
select * from test where status=:a;
select * from table(dbms_xplan.display_cursor('gf7f22fv3f5mg',null));


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gf7f22fv3f5mg, child number 0
-------------------------------------
select * from test where status=:a


Plan hash value: 1357081020


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    17 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST | 14820 |   347K|    17   (0)| 00:00:01 |
--------------------------------------------------------------------------

exec :a :='Inactive'
select * from test where status=:a;
select * from table(dbms_xplan.display_cursor('gf7f22fv3f5mg',null));


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gf7f22fv3f5mg, child number 0
-------------------------------------
select * from test where status=:a


Plan hash value: 1357081020


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    17 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST | 14820 |   347K|    17   (0)| 00:00:01 |
--------------------------------------------------------------------------



场景二：
alter system flush shared_pool;

exec :a :='Inactive'
select * from test where status=:a;
select * from table(dbms_xplan.display_cursor('gf7f22fv3f5mg',null));


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gf7f22fv3f5mg, child number 0
-------------------------------------
select * from test where status=:a


Plan hash value: 1357081020


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    17 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST | 14820 |   347K|    17   (0)| 00:00:01 |
--------------------------------------------------------------------------


exec :a :='Active'
select * from test where status=:a;
select * from table(dbms_xplan.display_cursor('gf7f22fv3f5mg',null));


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gf7f22fv3f5mg, child number 0
-------------------------------------
select * from test where status=:a


Plan hash value: 1314416543


-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |    29 |   696 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_ID_IND |    29 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
看来绑定变量窥探很可能会导致问题，因为它产生出的执行计划不是对所有值都好，取决于第一次硬解析时窥探的值，而11G出现了adaptive cursor sharing 可以解决这个问题，这个我们留下下一节说。

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

转载于:http://blog.itpub.net/22034023/viewspace-1218576/