且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事
您所在的位置：首页 > 20160516估计行数量for group operation

20160516估计行数量for group operation

更新时间：2022-09-08 12:25:48

[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特性造成的情况.生成新的子光标.

上一篇 : ：[20160517]11GR2Cursor_Sharing=force的bug下一篇 : [20160512]tnsnames.ora配置文件.txt

相关阅读

推荐文章

网站首页网站地图Copyright © 2021-2022 且购网

免责声明：本站文章部分来源于互联网,如有侵权请联系站长,我们将在72小时内删除。