而从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_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 /

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

注意：在这里我们要验证oracle会为不同绑定变量生成不同的执行计划时，不能使用set autotrace traceonly exp stat等其他方式。因为set autotrace得出的执行计划始终都是第一次生成的执行计划。我们通过plan baseline从侧面来验证它。当然，我们也可以通过设置sql_trace=true从而将执行计划转储出来进行验证。 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;

　　注意：这里，我们之所以要多执行几次，主要是因为如果只是执行一次或两次，oracle能够认识到你传入的绑定变量落在了第一次的绑定变量（12462）所在的cube之外，但是oracle认为你可能只是偶尔执行该绑定变量，所以并不一定会使用另外那个全表扫描的执行计划。多执行几次以后，你会发现consistent gets突然从1390直线下降到了715，这时就说明oracle开始使用新的全表扫描的执行计划了。

　　然后，这时我们再去查看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
　　select * from t1 where id=:v_id 7y7tt6xyhas1g 1 3617692013

　　我们发现，该SQL语句在内存里存在两条记录了，也就是存在两个子游标了，分别对应了不同的执行计划。同样，我们来看一下新产生的子游标，也就是child_number为1的执行计划是怎样的。 SQL> select * from table(dbms_xplan.display_cursor('7y7tt6xyhas1g',1));
　　PLAN_TABLE_OUTPUT
　　--------------------------------------------------------------------------------
　　SQL_ID 7y7tt6xyhas1g, child number 1
　　-------------------------------------
　　select * from t1 where id=:v_id
　　Plan hash value: 3617692013
　　--------------------------------------------------------------------------
　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
　　--------------------------------------------------------------------------
　　| 0 | SELECT STATEMENT | | | | 16 (100) | |
　　|* 1 | TABLE ACCESS FULL| T1 | 9974 | 204K | 16 (0) | 00:00:01 |
　　......

　　我们还可以从另外的角度来验证11g里的动态绑定变量窥视，也就是设置sql_trace的方式。这个方式比较简单，只要先发出：alter session set sql_trace=true以后，传入两个不同的绑定变量，然后分别就不同的绑定变量多执行几次。最后调用tkprof对跟踪文件进行分析。这里注意两个地方，第一是跟踪文件位于ADR中，不再位于user_dump_dest参数所指定的目录里了。就这里的跟踪文件而言，其所在位置缺省为：$ORACLE_HOME/diag/rdbms/<DB name>/<SID>/trace目录下；第二个要注意的是使用tkprof时，添加aggregate=no选项，缺省会将相同SQL语句合并，这样你就发现不到对于相同SQL语句的不同的执行计划了。

　　这里节选部分使用tkprof得到的文件内容，如下所示。 ......
　　SQL ID : 7y7tt6xyhas1g
　　select *
　　from
　　t1 where id=:v_id
　　......
　　Rows Row Source Operation
　　------- ---------------------------------------------------
　　10000 TABLE ACCESS BY INDEX ROWID T1 (cr=1390 pr=0 pw=0 time=446 us cost=2 size=21 card=1)
　　10000 INDEX RANGE SCAN IDX_T1 (cr=687 pr=0 pw=0 time=228 us cost=1 size=0 card=1)(object id 12463)
　　......
　　SQL ID : 7y7tt6xyhas1g
　　select *
　　from
　　t1 where id=:v_id
　　......
　　Rows Row Source Operation
　　------- ---------------------------------------------------
　　10000 TABLE ACCESS FULL T1 (cr=715 pr=0 pw=0 time=142 us cost=16 size=209454 card=9974)
　　......

　　从这里也可以很清楚的看到，对于不同的绑定变量，oracle能够自行选择是否应该生成更好的执行计划并使用该执行计划。

这样的处理，对于静态的表来说已经很完美了。
但是假如一个表在不断被insert ,update。一个以前落在一个cube中的变量可能在某段时间后本不应该再落在此cube中。ORACLE有什么机制能识别此种情况并为此变量产生新的执行计划与cube吗？
从上面的描述中没看到对这种情况的处理。

kevin.zhang 发表于 2010-11-18 14:02
这样的处理，对于静态的表来说已经很完美了。
但是假如一个表在不断被insert ,update。一个以前落在一个cu ...

这个是没有关系的，因为执行计划之和变量值相关，和记录无关，如果字段被update了，那么你在传如这个值时ORACLE可能就会选择一个不同的执行计划了。

kevin.zhang的意思是，变量值发生改变以后，原来不倾斜的变量值，现在倾斜了。
这个问题还是需要从统计信息的收集来解决。
DBMS_STATS.GATHER_DATABASE_STATS (
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type
                                                (get_param('ESTIMATE_PERCENT')),
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab          VARCHAR2 DEFAULT NULL,
   statid           VARCHAR2 DEFAULT NULL,
   options          VARCHAR2 DEFAULT 'GATHER',
   statown          VARCHAR2 DEFAULT NULL,
   gather_sys       BOOLEAN  DEFAULT TRUE,
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')));
no_invalidate
Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default.

如果统计信息发生比较大的改变，那么执行计划会失效。

疑问到最后始终没有使id=1的变成全表扫描，执行很多次也没有减少逻辑读，使用全表扫描，前面的实验都能做出来

怎么回事呢？是baseline的原因吗？

环境11.2.0.2.0
linux 64位5.6

1. exec :v_id := 1;
   
2. set autotrace traceonly stat;
   
3. 16:54:17 saup@PRI11G> select * from test2 where id=:v_id;
   
4. 
   
5. 70000 rows selected.
   
6. 
   
7. 
   
8. Execution Plan
   
9. ----------------------------------------------------------
   
10. Plan hash value: 1905508226
    
11. 
    
12. -----------------------------------------------------------------------------------------
    
13. | Id  | Operation                    | Name        | Rows        | Bytes | Cost (%CPU)| Time        |
    
14. -----------------------------------------------------------------------------------------
    
15. |   0 | SELECT STATEMENT            |                |    23 |   621 |     2   (0)| 00:00:01 |
    
16. |   1 |  TABLE ACCESS BY INDEX ROWID| TEST2        |    23 |   621 |     2   (0)| 00:00:01 |
    
17. |*  2 |   INDEX RANGE SCAN            | TEST2_IDX |    23 |        |     1   (0)| 00:00:01 |
    
18. -----------------------------------------------------------------------------------------
    
19. 
    
20. Predicate Information (identified by operation id):
    
21. ---------------------------------------------------
    
22. 
    
23.    2 - access("ID"=TO_NUMBER(:V_ID))
    
24. 
    
25. Note
    
26. -----
    
27.    - SQL plan baseline "SQL_PLAN_6mj1jqcybfuzbe2a98ff7" used for this statement
    
28. 
    
29. Statistics
    
30. ----------------------------------------------------------
    
31.           0  recursive calls
    
32.           0  db block gets
    
33.        9880  consistent gets
    
34.           0  physical reads
    
35.           0  redo size
    
36.     3142815  bytes sent via SQL*Net to client
    
37.       51845  bytes received via SQL*Net from client
    
38.        4668  SQL*Net roundtrips to/from client
    
39.           0  sorts (memory)
    
40.           0  sorts (disk)
    
41.       70000  rows processed
    

复制代码

1. SQL> select count(1) from test2;
   
2. 
   
3.   COUNT(1)
   
4. ----------
   
5.      73173
   
6. 
   
7. SQL> select count(1) from test2 where id=1;
   
8. 
   
9.   COUNT(1)
   
10. ----------
    
11.      70000
    
12. 
    
13. SQL> select sql_text,sql_id,child_number,plan_hash_value from v$sql where sql_text like 'select * from test2%';
    
14. 
    
15. SQL_TEXT                                                                         SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE
    
16. -------------------------------------------------------------------------------- ------------- ------------ ---------------
    
17. select * from test2 where id=:v_id                                               55d5cn125hxb0            0      1905508226
    
18. 
    
19. SQL> select * from table(dbms_xplan.display_cursor('55d5cn125hxb0',0));
    
20. 
    
21. PLAN_TABLE_OUTPUT
    
22. --------------------------------------------------------------------------------
    
23. SQL_ID  55d5cn125hxb0, child number 0
    
24. -------------------------------------
    
25. select * from test2 where id=:v_id
    
26. Plan hash value: 1905508226
    
27. --------------------------------------------------------------------------------
    
28. | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
    
29. --------------------------------------------------------------------------------
    
30. |   0 | SELECT STATEMENT            |           |       |       |   563 (100)|
    
31. |   1 |  TABLE ACCESS BY INDEX ROWID| TEST2     | 70003 |  1845K|   563   (1)| 0
    
32. |*  2 |   INDEX RANGE SCAN          | TEST2_IDX | 70003 |       |   227   (1)| 0
    
33. --------------------------------------------------------------------------------
    
34. Predicate Information (identified by operation id):
    
35. ---------------------------------------------------
    
36.    2 - access("ID"=:V_ID)
    
37. Note
    
38. 
    
39. PLAN_TABLE_OUTPUT
    
40. --------------------------------------------------------------------------------
    
41. -----
    
42.    - SQL plan baseline SQL_PLAN_6mj1jqcybfuzbe2a98ff7 used for this statement
    
43. 
    
44. 23 rows selected

复制代码

查看baseline

1. 
   
2. 
   
3. SQL> select SQL_HANDLE,SQL_TEXT,PLAN_NAME,ORIGIN,VERSION,ENABLED,ACCEPTED,FIXED from dba_sql_plan_baselines where sql_text like 'select * from test2%';
   
4. 
   
5. SQL_HANDLE            SQL_TEXT                            PLAN_NAME                      ORIGIN         VERSION     ENABLED ACCEPTED FIXED
   
6. --------------------- ------------------------------------------------------------------ -------------- ----------- ------- -------- -----
   
7. SQL_69c431b33cb76beb  select * from test2 where id=:v_id  SQL_PLAN_6mj1jqcybfuzb99963deb AUTO-CAPTURE   11.2.0.2.0  YES     NO       NO
   
8. SQL_69c431b33cb76beb  select * from test2 where id=:v_id  SQL_PLAN_6mj1jqcybfuzbe2a98ff7 AUTO-CAPTURE   11.2.0.2.0  YES     YES      NO
   
9. 
   

复制代码

看结果应该是baseline的问题，但是下面的几个点要注意：
1、baseline问题
2、一定要收集统计信息，特别是柱状图还是要注意收集

oraunix 发表于 2012-6-8 18:04
看结果应该是baseline的问题，但是下面的几个点要注意：
1、baseline问题
2、一定要收集统计信息，特别是柱 ...

我有收集统计信息

1. 17:50:12 saup@PRI11G> exec dbms_stats.gather_table_stats('saup','test2',method_opt => 'for columns id size 254',cascade => true);

复制代码

我猜11gR2与老师2010年做的测试版本不同

在这篇文章里
http://www.jiagulun.com/forum.php?mod=viewthread&tid=110

有用到
如果我们要手工删除plan baseline里的执行计划，则可以调用dbms_spm里的存储过程来实现。
SQL> var cnt number;
SQL> exec :cnt := dbms_spm.purge_sql_plan_baseline('SYS_SQL_abc0a2c042fa089c');

我现在版本已经没有purge，用的dbms_spm.drop_sql_plan_baseline；

而且dbms_spm.alter_sql_plan_baseline(attribute_value => 这个值没有accepted

需要设置fixed为yes，再演化，

在本章里此句不能执行

1. hr@ora11g > begin
   
2. 　　2 :cnt := dbms_spm.alter_sql_plan_baseline(
   
3. 　　3 sql_handle => 'SYS_SQL_ea05bbed6f2f670c',
   
4. 　　4 plan_name => 'SYS_SQL_PLAN_6f2f670cdbd90e8e',
   
5. 　　5 attribute_name => 'ACCEPTED', attribute_value => 'YES');
   
6. 　　6 end;
   
7. 　　7 /

复制代码

这个实验我会在课堂上给大家演示一下，Oracle不同的版本变化还是很大的。
因此要更多的参考Oracle官方文档。