Oracle11g新特性之动态变量窥视

欢迎进入Oracle社区论坛，与200万技术人员互动交流 >>进入 1. 11g之前的绑定变量窥视 我们都知道，为了能够让SQL语句共享执行计划，oracle始终都是强调在进行应用系统的设计时，必须使用绑定变量，也就是用一个变量来代替原来出现在SQL语句里的字面值。比如

欢迎进入Oracle社区论坛，与200万技术人员互动交流 >>进入

        1. 11g之前的绑定变量窥视

        我们都知道，为了能够让SQL语句共享执行计划，oracle始终都是强调在进行应用系统的设计时，必须使用绑定变量，也就是用一个变量来代替原来出现在SQL语句里的字面值。比如，对于下面三条SQL语句来说：

         select col1 from t where col2 = 1;
        select col1 from t where col2 = 2;
        select col1 from t where col2 = 3;

        我们可以看到，这三条SQL语句几乎一样，只有最后where条件里的字面值（分别是1、2、3）不同而已。但是如果写成这个样子，则oracle是不知道这三条SQL语句是一样的，仍然把它们当作三条完全不同的SQL语句，从而在shared pool里进行硬解析，并生成最终的执行计划。但是我们会发现，这三个执行计划可能都是一样的，因此后面两次生成执行计划的工作可能是完全不必要的，这在典型的OLTP环境中更是如此。由于解析本身属于CPU密集型操作，因此为了降低对CPU的消耗，oracle建议将这样的SQL写成：

         select col1 from t where col2 = :v1;

        然后，分别将1、2、3传递给v1，这样的话，只需要第一次传入1时进行解析即可。而后面执行2、3时，由于SQL文本本身没有变化，因此直接把执行计划拿来使用即可，不需要再次生成执行计划。

        但是，生成执行计划本身是基于概率的理论，在不访问具体表里的数据的前提下，根据你的where条件，来猜测返回的记录数大概是多少，从而判断应该采用怎样的访问路径。很明显，这是一定要参照具体的where条件里的值才能进行猜测的。这样就与节省CPU的初衷产生了矛盾，因为节省CPU的关键是使用绑定变量，你一旦使用了绑定变量，则oracle岂不是不知道你具体的字面值了吗？

        为了解决这一问题，oracle引入了绑定变量窥视。所谓绑定变量窥视，就是指oracle在第一次解析SQL语句的时候（也就是说该SQL第一次传入shared pool），会将你输入的绑定变量的值带入SQL语句里，从而参考你的字面值来猜测该SQL大概会返回多少条记录，从而得到优化的执行计划。然后，以后再次执行相同的SQL语句时，不再考虑你所输入的绑定变量的值，直接取出第一次生成的绑定变量。

        但是，很可惜的是，使用绑定变量从而共享游标与SQL优化是两个矛盾的目标。Oracle使用绑定变量的前提，是oracle认为大部分的列的数据都是分布比较均匀的。从而，使用第一次的绑定变量的值所得到的执行计划，大多数情况下都能适用于该绑定变量的其他的值。很明显，如果第一次传入的绑定变量的值恰好占整个数据量的百分比较高，从而导致全表扫描的执行计划。而后来传入的绑定变量的值都占整个数据量的百分比都很低，则应该走索引扫描会更好的，但是由于使用了绑定变量，从而oracle并不会再去看你的绑定变量的值，而是直接拿全表扫描的执行计划来用。这时，由于使用了绑定变量，虽然我们达到了游标共享，从而节省CPU的目的，但是SQL的执行计划却不够优化了。

        那么我们如何在绑定变量和SQL优化之间进行取舍呢？在OLTP应用中，由于并发性较高，CPU上的争用会比较严重，同时SQL本身执行时间较短，涉及到的数据量较少，解析所占的时间在整个SQL执行时间中占的比例较高，而花在I/O上的时间占的比例较低。因此尽管绑定变量会有SQL不够优化的问题，还是建议使用绑定变量。但是在DSS应用和数据仓库应用中，由于并发性较低，CPU上的争用较轻，同时SQL语句的执行时间都很长，而且主要时间花在等待I/O上，而解析占的比重较低，这时优化SQL执行计划的重要性就体现出来了。因此，建议不要使用绑定变量，而直接使用字面值。但是大多数的情况都是混合应用，既有OLTP又有数据仓库，这时就很难完美的解决该问题了。

        我们先来看一下11g之前的绑定变量窥视是如何工作的，以10g为例。

        我们先创建一个表，使得其含有的数据分布不均匀，并在该表上创建一个索引。

         hr@ora10g > create table t1 as select object_id as id,object_name from dba_objects;
        hr@ora10g > update t1 set id=1 where rownum        hr@ora10g > commit;
        hr@ora10g > create index idx_t1 on t1(id);

        这样，该表里id为的1记录有一万条，而id为其他值的记录都只有一条。从而，我们构建出一个分布不均匀的测试用表。然后，我们收集一下统计信息。注意，这里要收集直方图，为的是要让CBO知道id列上的数据分布不均匀。

         hr@ora10g> begin
        2 dbms_stats.gather_table_stats(
        3 user,
        4 't1',
        5 cascade => true,
        6 method_opt => 'for columns id size 254'
        7 );
        8 end;
        9 /

        我们找到表t1里最大的id，然后以该id作为第一个绑定变量传入，可以想象，该绑定变量将导致走索引。注意，我们这里设定的优化器目标为all_rows。

         hr@ora11g > select max(id) from t1;
        MAX(ID)
        ----------
        13871
        hr@ora10g> alter system flush shared_pool;
        hr@ora10g> var v_id number;
        hr@ora10g> var v_sql_id varchar2(20);
        hr@ora10g> exec :v_id := 13871;
        hr@ora10g> select * from t1 where id=:v_id;
        此处省略查询结果
        hr@ora10g > begin
        2 select sql_id into :v_sql_id from v$sql
        3 where sql_text like 'select * from t1 where id=:v_id%';
        4 end;
        5 /
        hr@ora10g > select * from table(dbms_xplan.display_cursor(:v_sql_id));
        PLAN_TABLE_OUTPUT
        --------------------------------------------------------------------------------
        SQL_ID djwq30cpbcz7k, child number 0
        -------------------------------------
        select * from t1 where id=:v_id
        Plan hash value: 50753647
        --------------------------------------------------------------------------------
        | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
        --------------------------------------------------------------------------------
        | 0 | SELECT STATEMENT | | | | 11 (100) |
        | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1365 | 28665| 11 (0) | 00:00:01
        |* 2 | INDEX RANGE SCAN | IDX_T1 | 1365 | | 3 (0) | 00:00:01
        --------------------------------------------------------------------------------
        ......
        hr@ora10g> exec :v_id := 1;
        hr@ora10g> select * from t1 where id=:v_id;
        此处省略查询结果
        hr@ora10g > begin
        2 select sql_id into :v_sql_id from v$sql
        3 where sql_text like 'select * from t1 where id=:v_id%';
        4 end;
        5 /
        hr@ora10g > select * from table(dbms_xplan.display_cursor(:v_sql_id));
        PLAN_TABLE_OUTPUT
        --------------------------------------------------------------------------------
        SQL_ID djwq30cpbcz7k, child number 0
        -------------------------------------
        select * from t1 where id=:v_id
        Plan hash value: 50753647
        --------------------------------------------------------------------------------
        | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
        --------------------------------------------------------------------------------
        | 0 | SELECT STATEMENT | | | | 11 (100) |
        | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1365 | 28665| 11 (0) | 00:00:01
        |* 2 | INDEX RANGE SCAN | IDX_T1 | 1365 | | 3 (0) | 00:00:01
        --------------------------------------------------------------------------------

        从上面结果可以看出，在为绑定变量传入第一个值为13871时，由于返回的记录条数较少，导致走索引扫描。当我们第二次传入绑定变量值1时，oracle不再生成新的执行计划，而直接拿索引扫描的执行路径来用。

        但是，如果先传入1的绑定变量值，然后再传入13871的绑定变量值时，会怎样？我们继续测试。

         hr@ora10g> alter system flush shared_pool;
        hr@ora10g> set autotrace traceonly exp stat;
        hr@ora10g> exec :v_id := 1;
        hr@ora10g> select * from t1 where id=:v_id;
        hr@ora10g > begin
        2 select sql_id into :v_sql_id from v$sql
        3 where sql_text like 'select * from t1 where id=:v_id%';
        4 end;
        5 /
        hr@ora10g > select * from table(dbms_xplan.display_cursor(:v_sql_id));
        PLAN_TABLE_OUTPUT
        --------------------------------------------------------------------------------
        SQL_ID djwq30cpbcz7k, child number 0
        -------------------------------------
        select * from t1 where id=:v_id
        Plan hash value: 3617692013
        --------------------------------------------------------------------------
        | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
        --------------------------------------------------------------------------
        | 0 | SELECT STATEMENT | | | | 13 (100)| |
        |* 1 | TABLE ACCESS FULL | T1 | 8738 | 179K | 13 (0) | 00:00:01 |
        --------------------------------------------------------------------------
        ......
        hr@ora10g > exec :v_id := 13871;
        hr@ora10g > select * from t1 where id=:v_id;
        hr@ora10g > begin
        2 select sql_id into :v_sql_id from v$sql
        3 where sql_text like 'select * from t1 where id=:v_id%';
        4 end;
        5 /
        hr@ora10g > select * from table(dbms_xplan.display_cursor(:v_sql_id));
        PLAN_TABLE_OUTPUT
        --------------------------------------------------------------------------------
        SQL_ID djwq30cpbcz7k, child number 0
        -------------------------------------
        select * from t1 where id=:v_id
        Plan hash value: 3617692013
        --------------------------------------------------------------------------
        | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
        --------------------------------------------------------------------------
        | 0 | SELECT STATEMENT | | | | 13 (100)| |
        |* 1 | TABLE ACCESS FULL | T1 | 8738 | 179K | 13 (0) | 00:00:01 |
        --------------------------------------------------------------------------

        很明显，先传入1的绑定变量时将导致生成的执行计划走全表扫描。后面传入的13871的绑定变量的最佳执行路径应该是索引扫描，但是由于CBO并不知道这一点，而是直接拿第一次生成的执行计划来用了，于是也走全表扫描了。

[1] [2]