11g新特性之基数反馈（Cardinality Feedback）
由于表上缺少统计信息，或者统计信息陈旧等情况，会造成优化器生成sql语句执行计是无法评估出一个准确的执行计划，为了纠正这种情况，对于重复执行的sql语句，基数反馈可以根据语句执行时实际的基数信息重新优化执行计划。
该特性受影藏参数 _optimizer_use_feedback 控制。并且系统参数 statistics_level 设置为 ALL（可以在会话级别单独设置），或者在sql语句中加提示/*+ gather_plan_statistics */ 。
过程：
当语句第一次执行的时候，优化器生成初始的执行计划。
优化器在以下情况下会监控语句执行时的统计信息：
1.表上没有统计信息（动态采样虽然打开但是统计信息也不准确）。
2.多个合并或分开的谓词条件。
3.谓词包含复杂的操作符导致优化器没法评估选择性。
在语句执行的后期，优化器对每个操作比较初始的基数评估和返回的行数，如果评估出的基数和实际的行数相去甚远，优化器将存储正确的基数给后续的执行使用。
当查询第二次执行的时候，优化器会使用之前存储的基数去生成更准确的执行计划。
制造测试数据
create table testtab as select * from dba_objects;
insert into testtab select * from dba_objects;
insert into testtab select * from dba_objects;
create index idx_test testtab(owner);
这里不收集表的统计信息,第一次执行sql语句
select /*wxc*/count(distinct owner) from testtab;
查看执行计划
SQL_ID 51d4saacad5ac, child number 0 ------------------------------------- select /*wxc*/count(distinct owner) from testtab Plan hash value: 3856531508 ------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 39306 (100)| | | | | | 1 | SORT AGGREGATE | | 1 | 17 | | | | | | | 2 | VIEW | VW_DAG_0 | 306M| 4964M| 39306 (53)| 00:07:52 | | | | | 3 | HASH GROUP BY | | 306M| 4964M| 39306 (53)| 00:07:52 | 1036K| 1036K| 8573K (0)| | 4 | TABLE ACCESS FULL| TESTTAB | 306M| 4964M| 20802 (11)| 00:04:10 | | | | ------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$C33C846D 2 - SEL$5771D262 / VW_DAG_0@SEL$C33C846D 3 - SEL$5771D262 4 - SEL$5771D262 / TESTTAB@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') ALL_ROWS OUTLINE_LEAF(@"SEL$5771D262") TRANSFORM_DISTINCT_AGG(@"SEL$1") OUTLINE_LEAF(@"SEL$C33C846D") OUTLINE(@"SEL$1") OUTLINE(@"SEL$5771D262") TRANSFORM_DISTINCT_AGG(@"SEL$1") NO_ACCESS(@"SEL$C33C846D" "VW_DAG_0"@"SEL$C33C846D") FULL(@"SEL$5771D262" "TESTTAB"@"SEL$1") USE_HASH_AGGREGATION(@"SEL$5771D262") END_OUTLINE_DATA */ Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT("ITEM_1")[22] 2 - "ITEM_1"[VARCHAR2,30] 3 - "OWNER"[VARCHAR2,30] 4 - "OWNER"[VARCHAR2,30] Note ----- - dynamic sampling used for this statement (level=2) - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level
可见基数评估是根据动态采样得出的.
第二次关闭动态采样,设置参数statistics_level为all.使得优化器可以收集基数反馈统计信息
alter session set statistics_level=all;
执行计划如下.
SQL_ID 51d4saacad5ac, child number 1 ------------------------------------- select /*wxc*/count(distinct owner) from testtab Plan hash value: 3856531508 ------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 304 (100)| | 1 |00:00:01.39 | 68721 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 17 | | | 1 |00:00:01.39 | 68721 | | | | | 2 | VIEW | VW_DAG_0 | 1 | 89603 | 1487K| 304 (2)| 00:00:04 | 30 |00:00:01.39 | 68721 | | | | | 3 | HASH GROUP BY | | 1 | 89603 | 1487K| 304 (2)| 00:00:04 | 30 |00:00:01.39 | 68721 | 1036K| 1036K| 3522K (0)| | 4 | TABLE ACCESS FULL| TESTTAB | 1 | 89603 | 1487K| 300 (1)| 00:00:04 | 4812K|00:00:00.44 | 68721 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$C33C846D 2 - SEL$5771D262 / VW_DAG_0@SEL$C33C846D 3 - SEL$5771D262 4 - SEL$5771D262 / TESTTAB@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') OPT_PARAM('optimizer_dynamic_sampling' 0) ALL_ROWS OUTLINE_LEAF(@"SEL$5771D262") TRANSFORM_DISTINCT_AGG(@"SEL$1") OUTLINE_LEAF(@"SEL$C33C846D") OUTLINE(@"SEL$1") OUTLINE(@"SEL$5771D262") TRANSFORM_DISTINCT_AGG(@"SEL$1") NO_ACCESS(@"SEL$C33C846D" "VW_DAG_0"@"SEL$C33C846D") FULL(@"SEL$5771D262" "TESTTAB"@"SEL$1") USE_HASH_AGGREGATION(@"SEL$5771D262") END_OUTLINE_DATA */ Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT("ITEM_1")[22] 2 - "ITEM_1"[VARCHAR2,30] 3 - "OWNER"[VARCHAR2,30] 4 - "OWNER"[VARCHAR2,30]
可见估算的基数(E-Rows)和实际返回的行数相去甚远。
再次执行sql语句，可见基数反馈这个特性已经生效了。
SQL_ID 51d4saacad5ac, child number 2 ------------------------------------- select /*wxc*/count(distinct owner) from testtab Plan hash value: 3856531508 ------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 534 (100)| | 1 |00:00:02.38 | 68721 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 17 | | | 1 |00:00:02.38 | 68721 | | | | | 2 | VIEW | VW_DAG_0 | 1 | 4812K| 78M| 534 (45)| 00:00:07 | 30 |00:00:02.38 | 68721 | | | | | 3 | HASH GROUP BY | | 1 | 4812K| 78M| 534 (45)| 00:00:07 | 30 |00:00:02.38 | 68721 | 1036K| 1036K| 22M (0)| | 4 | TABLE ACCESS FULL| TESTTAB | 1 | 4812K| 78M| 305 (2)| 00:00:04 | 4812K|00:00:00.42 | 68721 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$C33C846D 2 - SEL$5771D262 / VW_DAG_0@SEL$C33C846D 3 - SEL$5771D262 4 - SEL$5771D262 / TESTTAB@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') OPT_PARAM('optimizer_dynamic_sampling' 0) ALL_ROWS OUTLINE_LEAF(@"SEL$5771D262") TRANSFORM_DISTINCT_AGG(@"SEL$1") OUTLINE_LEAF(@"SEL$C33C846D") OUTLINE(@"SEL$1") OUTLINE(@"SEL$5771D262") TRANSFORM_DISTINCT_AGG(@"SEL$1") NO_ACCESS(@"SEL$C33C846D" "VW_DAG_0"@"SEL$C33C846D") FULL(@"SEL$5771D262" "TESTTAB"@"SEL$1") USE_HASH_AGGREGATION(@"SEL$5771D262") END_OUTLINE_DATA */ Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT("ITEM_1")[22] 2 - "ITEM_1"[VARCHAR2,30] 3 - "OWNER"[VARCHAR2,30] 4 - "OWNER"[VARCHAR2,30] Note ----- - cardinality feedback used for this statement
可以看出优化器可以采用更准确的基数去生成执行计划了。
posted on 2016-12-13 21:25 wangxingc 阅读(1624) 评论(0) 编辑 收藏 举报
