[20160516]How CBO estimate the row source for group operation.txt
--参考连接http://blog.itpub.net/17203031/viewspace-751065/,重复测试:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
-------------------- -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table t as select * from dba_objects;
Table created.
SCOTT@book> select count(distinct(owner)), count(distinct(object_type)) from t;
COUNT(DISTINCT(OWNER)) COUNT(DISTINCT(OBJECT_TYPE))
---------------------- ----------------------------
29 46
SCOTT@book> select count(*) from t;
COUNT(*)
----------
86945
execute sys.dbms_stats.gather_table_stats ( OwnName => nvl('',user),TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false);
SCOTT@book> alter session set statistics_level=all ;
Session altered.
2.测试一:
select owner, count(*) from t group by owner;
Plan hash value: 47235625
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 349 (100)| | 29 |00:00:00.05 | 1244 | 1242 | | | |
| 1 | HASH GROUP BY | | 1 | 29 | 174 | 349 (1)| 00:00:05 | 29 |00:00:00.05 | 1244 | 1242 | 7390K| 3198K| 2246K (0)|
| 2 | TABLE ACCESS FULL| T | 1 | 86945 | 509K| 347 (1)| 00:00:05 | 86945 |00:00:00.02 | 1244 | 1242 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
select object_type, count(*) from t group by object_type;
Plan hash value: 47235625
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 350 (100)| | 46 |00:00:00.05 | 1244 | 1242 | | | |
| 1 | HASH GROUP BY | | 1 | 46 | 414 | 350 (2)| 00:00:05 | 46 |00:00:00.05 | 1244 | 1242 | 7390K| 3198K| 2517K (0)|
| 2 | TABLE ACCESS FULL| T | 1 | 86945 | 764K| 347 (1)| 00:00:05 | 86945 |00:00:00.02 | 1244 | 1242 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
SCOTT@book> select column_name, num_distinct, DENSITY from dba_tab_columns where table_name='T' and owner=user;
COLUMN_NAME NUM_DISTINCT DENSITY
-------------------- ------------ ----------
OWNER 29 .034482759
OBJECT_NAME 52380 .000019091
SUBOBJECT_NAME 149 .006711409
OBJECT_ID 86941 .000011502
DATA_OBJECT_ID 9043 .000110583
OBJECT_TYPE 46 .02173913
CREATED 1035 .000966184
LAST_DDL_TIME 1306 .000765697
TIMESTAMP 1335 .000749064
STATUS 2 .5
TEMPORARY 2 .5
GENERATED 2 .5
SECONDARY 2 .5
NAMESPACE 21 .047619048
EDITION_NAME 0 0
15 rows selected.
--可以发现单个字段的gruop by返回的函数很好估计,就是对应统计信息的单个字段NUM_DISTINCT.
3.测试二:
--如果多个字段呢?
select owner, object_type, count(*) from t group by owner, object_type;
Plan hash value: 47235625
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 350 (100)| | 271 |00:00:00.06 | 1244 | 1242 | | | |
| 1 | HASH GROUP BY | | 1 | 944 | 14160 | 350 (2)| 00:00:05 | 271 |00:00:00.06 | 1244 | 1242 | 7943K| 2924K| 2517K (0)|
| 2 | TABLE ACCESS FULL| T | 1 | 86945 | 1273K| 347 (1)| 00:00:05 | 86945 |00:00:00.02 | 1244 | 1242 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
29*46/944=1.4131355932203389835
29*46/sqrt(2)=943.280446102917
--做一个猜测是2个字段的NUM_DISTINCT相乘再除以根号2.
select owner,NAMESPACE, count(*) from t group by owner, NAMESPACE;
Plan hash value: 47235625
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 350 (100)| | 129 |00:00:00.06 | 1244 | 1243 | | | |
| 1 | HASH GROUP BY | | 1 | 431 | 3879 | 350 (2)| 00:00:05 | 129 |00:00:00.06 | 1244 | 1243 | 7648K| 3061K| 2498K (0)|
| 2 | TABLE ACCESS FULL| T | 1 | 86945 | 764K| 347 (1)| 00:00:05 | 86945 |00:00:00.03 | 1244 | 1243 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
29*21/431=1.4129933944315545243
29*21/sqrt(2)=430.628029742636
--基本猜测正确.不过第2次执行计划如下:
Plan hash value: 47235625
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 350 (100)| | 129 |00:00:00.06 | 1244 | 1242 | | | |
| 1 | HASH GROUP BY | | 1 | 129 | 1161 | 350 (2)| 00:00:05 | 129 |00:00:00.06 | 1244 | 1242 | 7648K| 3061K| 2519K (0)|
| 2 | TABLE ACCESS FULL| T | 1 | 86945 | 764K| 347 (1)| 00:00:05 | 86945 |00:00:00.03 | 1244 | 1242 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Note
-----
- cardinality feedback used for this statement
--这个是11G的cardinality feedback特性造成的情况.
3.很明显如果在多一个字段就是再多除以一个根号2.
select owner,object_type,NAMESPACE, count(*) from t group by owner, object_type,NAMESPACE;
Plan hash value: 47235625
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 576 (100)| | 271 |00:00:00.07 | 1244 | 1242 | | | |
| 1 | HASH GROUP BY | | 1 | 14007 | 246K| 2400K| 576 (1)| 00:00:07 | 271 |00:00:00.07 | 1244 | 1242 | 8229K| 2834K| 3022K (0)|
| 2 | TABLE ACCESS FULL| T | 1 | 86945 | 1528K| | 347 (1)| 00:00:05 | 86945 |00:00:00.03 | 1244 | 1242 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
29*46*21/14007=2.00000000000000000000
29*46*21/sqrt(2)/sqrt(2)=14007.000000001882
--几乎完美的吻合.
--第2次执行计划如下:
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID dpxb1yyhzhxqn, child number 1
-------------------------------------
select owner,object_type,NAMESPACE, count(*) from t group by owner,
object_type,NAMESPACE
Plan hash value: 47235625
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 350 (100)| | 271 |00:00:00.06 | 1244 | 1242 | | | |
| 1 | HASH GROUP BY | | 1 | 271 | 4878 | 350 (2)| 00:00:05 | 271 |00:00:00.06 | 1244 | 1242 | 8229K| 2834K| 2518K (0)|
| 2 | TABLE ACCESS FULL| T | 1 | 86945 | 1528K| 347 (1)| 00:00:05 | 86945 |00:00:00.03 | 1244 | 1242 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Note
-----
- cardinality feedback used for this statement
--这个是11G的cardinality feedback特性造成的情况.生成新的子光标.