从Oracle优化器发展历程上看，CBO已经取代RBO，成为今后优化器发展的主流。在CBO时代，执行计划SEP生成更加灵活、更加符合实际数据情况，执行效率更高。
在笔者之前的系列文章中，反复强调过CBO工作三个重要输入：数据统计量、优化器成本公式和内部调节参数。三者应该说是会直接影响到CBO生成执行计划的准确性。
在三个重要输入中，数据统计量是可变性最大的一方面因素。统计量缺失、过期和失真，是我们进入10g之后遇到执行计划问题中最常见的部分。为了应对这些问题，Oracle也在不断进行一些尝试手段，本篇就介绍其中一个Cardinality Feedback。
1、失真的成本计算值
我们从一个简单问题谈起：究竟什么样的情况会引起执行计划的错误？从笔者角度来看两方面的原因都可能造成问题：统计量缺失失真和SQL语句本身特性。
ü 统计量失真：例如新数据表从来没有进行过统计量收集，或者距离上次收集之后，数据取值和结构发生了很大的变化。失真的统计量计算出错误的cost值，引起SEP错误；
ü SQL语句本身特性：绝大多数的统计量信息都是基于独立列统计的。而我们的SQL语句，很多时候where条件是相关的，或者连接引起的相关性。在这样的情况下，CBO估算结果集合的时候，是不可能得到准确的结果的；
基于这些特殊的情况，Oracle实际上是进行了很多的优化手段。动态采样（dynamic sampling）可以解决统计量缺失和相关列取值问题，而Oracle 11g推出的组合统计量（Multi-Column Statistics）也在试图解决相关列问题。
其实，在11g中，Oracle同时还开启了一个新的功能Cardinality Feedback，提供自适应方式的执行计划调节。
Cardinality是执行计划的一个重要要素，最直接的表示就是某个特定操作获取到的数据集合行数。Cardinality在SEP成本计算过程中十分重要，直接决定了IO数据量。
所谓的Cardinality Feedback，本质上就是一种基于自适应模型的执行计划调整机制。当对一个SQL语句，第一次生成执行计划之后，其计算的成本cost是依据估算出的cardinality得出的。当执行这个计划后，Oracle就会得到真正这个SQL的执行计划结果，并且用真实的结果集合来更新执行计划中的Cardinality。在第二次生成时候，就可以使用更加真实的结果来确定了。
在Oracle 11g中，Cardinality Feedback功能默认开启，控制参数是一个隐含参数“_optimizer_use_feedback”。下面，我们根据一系列的实验，来进行测试该功能。
2、环境准备
我们选择Oracle 11gR2进行测试，构建实验数据表T。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
--构建数据表T
SQL> create table t as select * from dba_objects where 1=0;
Table created.
SQL> create index idx_t_owner on t(owner);
Index created.
SQL> insert into t select * from dba_objects;
72461 rows created.
SQL> commit;
Commit complete.
此时，我们检查隐含参数“_optimizer_use_feedback”，默认值为true。表示启用cardinality feedback。
SQL> select
2 x.ksppinm name,
3 y.ksppstvl value,
4 y.ksppstdf isdefault,
5 decode(bitand(y.ksppstvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE') ismod,
6 decode(bitand(y.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadj
7 from
8 sys.x$ksppi x,
9 sys.x$ksppcv y
10 where
11 x.inst_id = userenv('Instance') and
12 y.inst_id = userenv('Instance') and
13 x.indx = y.indx and
14 x.ksppinm like '%use_feedback%'
15 order by
16 translate(x.ksppinm, ' _', ' ');
NAME VALUE ISDEFAULT ISMOD ISADJ
------------------------------ ---------- --------- ---------- -----
_optimizer_use_feedback TRUE TRUE FALSE FALSE
3、无统计量时执行计划
Cardinality Feedback起作用的两个时点，首先是没有统计量，其次是SQL估算Row数值困难。我们先看无统计量的情况。
--无统计量
SQL> select SAMPLE_SIZE, LAST_ANALYZED from dba_tables where wner='SYS' and table_name='T';
SAMPLE_SIZE LAST_ANALYZED
----------- -------------
--Autotrace跟踪
SQL> select /*+ DEMO */ * from t where wner='SCOTT';
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1516787156
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 1242 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 6 | 1242 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 6 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> select version_count from v$sqlarea where sql_text like 'select /*+ DEMO */%';
VERSION_COUNT
-------------
1
注意，此时虽然没有统计量，但是Dynamic Sampling功能被启用。Cardinality Feedback是不会出现的！
4、关闭Dynamic Sampling
那么，如果我们统计量，关闭Dynamic Sampling，是否就可以启用Cardinality Feedback了呢？
首先，我们清理一下shared Pool，删除统计量。
SQL> alter system flush shared_pool;
System altered
SQL> alter system flush buffer_cache;
System altered
SQL> exec dbms_stats.delete_table_stats(user,'T',cascade_columns => true,cascade_indexes => true);
PL/SQL procedure successfully completed
关闭动态统计功能。
SQL> alter session set optimizer_dynamic_sampling=0;
Session altered.
执行新的SQL语句，查看执行计划情况。
--第一次执行
SQL> select /*+ DEMO-2 */ * from t where wner='SCOTT';
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1516787156
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 840 | 169K| 5 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 840 | 169K| 5 (0)|
00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 336 | | 1 (0)|
00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
注意：此时没有Dynamic Sampling信息！！
--第二次执行
SQL> select /*+ DEMO-2 */ * from t where wner='SCOTT';
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1516787156
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 840 | 169K| 5 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 840 | 169K| 5 (0)|
00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 336 | | 1 (0)|
00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
从autotrace中，我们没有看到任何不同。但是，我们从shared pool中，却看到了不同的情况。
SQL> select sql_id,version_count from v$sqlarea where sql_text like 'select /*+ DEMO-2 */%';
SQL_ID VERSION_COUNT
------------- -------------
dttcb0t4drju2 2
出现了两个子游标。在相同的父游标下，存在了两个子游标。说明生成了两个执行计划。我们直接从shared pool中抽取出来，如下：
--0号子游标；
SQL> select * from table(dbms_xplan.display_cursor(sql_id => 'dttcb0t4drju2',cursor_child_no => 0,format => 'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID dttcb0t4drju2, child number 0
-------------------------------------
select /*+ DEMO-2 */ * from t where wner='SCOTT'
Plan hash value: 1516787156
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 840 | 169K| 5 (0)|
|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 336 | | 1 (0)|
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
OPT_PARAM('optimizer_dynamic_sampling' 0)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."OWNER"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
Column Projection Information (identified by operation id):
（篇幅原因，有省略……）
52 rows selected
--1号子游标
SQL> select * from table(dbms_xplan.display_cursor(sql_id => 'dttcb0t4drju2',cursor_child_no => 1,format => 'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID dttcb0t4drju2, child number 1
-------------------------------------
select /*+ DEMO-2 */ * from t where wner='SCOTT'
Plan hash value: 1516787156
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 6 | 1242 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 6 | | 1 (0)|
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
OPT_PARAM('optimizer_dynamic_sampling' 0)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."OWNER"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
Column Projection Information (identified by operation id):
Note
-----
- cardinality feedback used for this statement
56 rows selected
上面的信息已经比较清楚了。虽然两次执行的都是索引路径，但是执行计划中的Rows（Cardinality）进行了调整，进而cost也发生了变化。
在两个执行计划的Outline中，都明确的写清楚Dynamic Sampling没有使用。而且，在第二个执行计划中，存在有“cardinality feedback used for this statement”的字样。说明：第二个执行计划是使用了Cardinality Feedback产生的。
5、关闭Cardinality Feedback
更加极端，我们关闭了Dynamic Sampling和Cardinality Feedback，看看执行计划情况。
SQL> alter system flush shared_pool;
System altered
SQL> alter system flush buffer_cache;
System altered
SQL> alter session set "_optimizer_use_feedback"=false;
Session altered.
执行SQL语句，执行两遍。
SQL> select /*+ DEMO-3 */ * from t where wner='SCOTT';
6 rows selected.
查看shared pool中的情况。
SQL> select sql_id,version_count from v$sqlarea where sql_text like 'select /*+ DEMO-3 */%';
SQL_ID VERSION_COUNT
------------- -------------
10cgfzba17t9g 1
SQL> select * from table(dbms_xplan.display_cursor(sql_id => '10cgfzba17t9g',cursor_child_no => 0,format => 'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 10cgfzba17t9g, child number 0
-------------------------------------
select /*+ DEMO-3 */ * from t where wner='SCOTT'
Plan hash value: 1516787156
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 840 | 169K| 5 (0)|
|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 336 | | 1 (0)|
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
OPT_PARAM('optimizer_dynamic_sampling' 0)
OPT_PARAM('_optimizer_use_feedback' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."OWNER"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
（篇幅原因，省略部分……）
53 rows selected
6、结论
应该说，在有Dynamic Sampling和Multi-Column Statistic的情况下，Cardinality Feedback这种自适应反馈方法是没有过多的用武之地，特别是笔者实验的无统计量情况。
在另一个方面，Cardinality Feedback也许会更加有效，就是复杂SQL条件下的Cardinality估算。如果一个SQL十分复杂，不断的进行Rows Source调整也是才是该方法的真正价值所在。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-735112/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-735112/
2556
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
