2.     11g之后的动态绑定变量窥视

    而从11g开始，这个尴尬的问题开始得到了改善。因此从11g开始，引入了所谓的自适应游标共享（Adaptive Cursor Sharing）。该特性是一个非常复杂的技术，用来平衡游标共享和SQL优化这两个矛盾的目标。11g里不会盲目的共享游标，而是会去查看每个绑定变量，并为不同的绑定变量来产生不同的执行计划。而oracle这么做的前提是，使用多个执行计划的所带来的收益，要比产生多个执行计划所引起的CPU开销要更大。

使用自适应游标共享时，会遵循下面的步骤：

1)       一条新的SQL语句第一次传入shared pool时，还是和以前一样，进行硬解析。而且进行绑定变量窥视，计算where条件各个列的selectivity，同时如果绑定变量所在的列上存在直方图的话，也会去参考该直方图来计算selectivity。该游标会被标记为是一个绑定敏感的游标（bind-sensitive cursor）。同时，oracle还会保留包含绑定变量的where条件的其他信息，比如selectivity等。Oracle会为该谓词的selectivity维持一个范围，oracle叫做立方体（cube）。只要传入的绑定变量所产生的selectivity落在该范围里面，也就是落在该cube里面，就不产生新的执行计划，而直接拿该cube所对应的执行计划来用。

2)       下次再次执行相同的SQL时，传入了新的绑定变量，假设使用新的绑定变量的谓词的selectivity落在已经存在的cube范围里，于是这次SQL的执行会使用该cube所对应的执行计划。

3)       相同的查询再次执行时，假设所使用的新的绑定变量导致这时候的selectivity不再落在已经存在的cube里了，于是也就找不到对应的执行计划。于是系统会进行一个硬解析，这将产生第二个新的执行计划。而且新的selectivity以及对应的cube也会保存下来。也就是说，这时，我们分别有两个cube以及两个执行计划。

4)       相同的查询再次执行时，假设所使用的新的绑定变量导致这时候的selectivity不落在现存的两个cube中的任何一个，所以系统又会进行硬解析。假设这时硬解析所产生的执行计划与第一次产生执行计划一样，也就是说，在第一次评估selectivity的cube时过于保守，导致cube过小，进而导致了这一次的不必要的硬解析。于是，oracle会将第一次产生的cube与这次产生的cube合并成一个新的更大的cube。那么，下次再次进行软解析的时候，如果selectivity落在新的cube里，则会使用第一次所产生的执行计划。

我们从这里可以看到，11g对这个问题的处理非常精彩。这样做的结果是，系统开始运行时，CPU消耗可能会比较严重，但是随着系统不断运行，cube的不断合并从而不断扩大，于是系统的CPU消耗会不断下降，同时执行计划也会更加的合理。

我们来做个试验进行验证。我们采用11g新引入的执行计划管理特性来验证该特性。

与10g中的测试一样，创建一个数据分布不均匀的表，在数据分布不均匀的列上创建索引，并收集统计信息，收集时注意要收集直方图，从而让CBO知道该列上的数据分布不均匀。

hr@ora11g > create table t1 as select object_id as id,object_name from dba_objects;

hr@ora11g > select count(*) from t1;    

  COUNT(*)

----------

     12064

hr@ora11g > update t1 set id=1 where rownum<=10000;

hr@ora11g > commit;    

hr@ora11g > create index idx_t1 on t1(id);

hr@ora11g > 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作为第一个绑定变量传入，可以想象，该绑定变量将导致走索引。

hr@ora11g > select max(id) from t1;

   MAX(ID)          

----------

     12462

       我们将optimizer_capture_plan_baselines设置为true，从而让oracle自动获取plan baseline。

hr@ora11g > alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true; 

hr@ora11g > alter system flush shared_pool;

hr@ora11g > var v_id number;

hr@ora11g > exec :v_id := 12462;

hr@ora11g > select * from t1 where id=:v_id;

hr@ora11g > select * from t1 where id=:v_id; 

       我们运行两遍select * from t1 where id=:v_id，从而让oracle捕获plan baseline。我们知道id为12462的记录只有一条，因此该SQL应该使用索引扫描。然后我们再为绑定变量传入1，我们知道id为1的记录有一万条，所以较好的执行计划不应该走已经生成的执行计划，而应该走全表扫描。

hr@ora11g > exec :v_id := 1;

hr@ora11g > set autotrace traceonly stat; 

--之所以设置stat是为了让该sql实际执行，但不要返回所有记录，

hr@ora11g > select * from t1 where id=:v_id;

hr@ora11g > select sql_handle,plan_name,origin,enabled,accepted

  2  from dba_sql_plan_baselines where sql_text like 'select * from t1%';

SQL_HANDLE                   PLAN_NAME                         ORIGIN          ENA   ACC

-----------------------   ----------------------------- -------------- ---  ---

SYS_SQL_ea05bbed6f2f670c  SYS_SQL_PLAN_6f2f670c844cb98a  AUTO-CAPTURE   YES  YES

SYS_SQL_ea05bbed6f2f670c   SYS_SQL_PLAN_6f2f670cdbd90e8e  AUTO-CAPTURE  YES  NO

       我们可以发现，现在该SQL语句存在两个执行计划了，其中第一个执行计划，也就是accepted为YES的执行计划为v_id等于12462得到的，而第二个执行计划，也就是accepted为NO的是由v_id等于1得到的。第二个执行计划还没有被加入plan baseline，所以优化器不会使用该执行计划。我们将第二个执行计划的accepted改为YES，从而让oracle考虑使用该计划。

hr@ora11g > var cnt number;

hr@ora11g > begin

  2  :cnt := dbms_spm.alter_sql_plan_baseline(

  3  sql_handle => 'SYS_SQL_ea05bbed6f2f670c',

  4  plan_name => 'SYS_SQL_PLAN_6f2f670cdbd90e8e',

  5  attribute_name  => 'ACCEPTED', attribute_value => 'YES');

  6  end;

  7  /

       我们来看一下这两个执行计划分别是怎样的。

SQL> select * from table(dbms_xplan.display_sql_plan_baseline

  2  ('SYS_SQL_ea05bbed6f2f670c','SYS_SQL_PLAN_6f2f670c844cb98a'));

......

--------------------------------------------------------------------------------

Plan hash value: 50753647

--------------------------------------------------------------------------------

| Id  | Operation                      | Name   | Rows  | Bytes | Cost (%CPU)| Time    |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |         |     6 |   126  |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |     6 |   126  |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN            | IDX_T1 |     6 |         |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

......

SQL> select * from table(dbms_xplan.display_sql_plan_baseline

  2  ('SYS_SQL_ea05bbed6f2f670c','SYS_SQL_PLAN_6f2f670cdbd90e8e'));

......

--------------------------------------------------------------------------------

Plan hash value: 3617692013

--------------------------------------------------------------------------

| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU) | Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |        |     6 |   126   |    16   (0) | 00:00:01 |

|*  1 |  TABLE ACCESS FULL | T1    |     6 |   126   |    16   (0) | 00:00:01 |

--------------------------------------------------------------------------

......

       很明显，第一个是索引扫描，第二个是全表扫描。同样，我们来看一下v$sql里该sql语句有几条记录。

hr@ora11g > select sql_text,sql_id,child_number,plan_hash_value

  2  from v$sql where sql_text like 'select * from t1 where%';

SQL_TEXT                                 SQL_ID          CHILD_NUMBER    PLAN_HASH_VALUE

---------------------------------   -------------  ------------    ----------------

select * from t1 where id=:v_id     7y7tt6xyhas1g              0               50753647

       可以看到，该SQL语句目前在内存里只存在一个执行计划，其plan hash value就等于我们在前面plan baseline里看到的第一个走索引的执行计划的hash value。我们把该执行计划显示出来进行确认。

hr@ora11g > select * from table(dbms_xplan.display_cursor('7y7tt6xyhas1g',0));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  7y7tt6xyhas1g, 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              |         |        |        |     2 (100) |            |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |     1 |    21  |     2   (0) | 00:00:01 |

|*  2 |   INDEX RANGE SCAN            | IDX_T1 |     1 |        |     1   (0) | 00:00:01 |

......

       结果很明显，正是走索引的执行计划。然后我们继续为帮定变量传入1，多执行几次。

hr@ora11g > exec :v_id := 1;

hr@ora11g > set autotrace traceonly stat; 

hr@ora11g > select * from t1 where id=:v_id;

hr@ora11g > select * from t1 where id=:v_id;

hr@ora11g > select * from t1 where id=:v_id;

       然后，这时我们再去查看v$sql里该sql语句有几条记录。

hr@ora11g > select sql_text,sql_id,child_number,plan_hash_value

  2  from v$sql where sql_text like 'select * from t1 where%';

SQL_TEXT                                 SQL_ID          CHILD_NUMBER    PLAN_HASH_VALUE

---------------------------------   -------------  ------------    ----------------

select * from t1 where id=:v_id     7y7tt6xyhas

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