由于表上缺少统计信息,或者统计信息陈旧等情况,会造成优化器生成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
可以看出优化器可以采用更准确的基数去生成执行计划了。