为什么要有列的统计信息

http://www.itpub.net/thread-1349832-1-1.html

在这个帖子中，楼主发现了一个问题

begin
dbms_stats.gather_table_stats(ownname => 'SCOTT',
tabname => 'T5',
METHOD_OPT => 'FOR COLUMNS SIZE AUTO');
end;
即使不指定列名或ALL，依然可以成功执行

但是，这样执行是不会收集列上的统计信息的，见下面的测试例子：

[@more@]列的统计信息还包含NDV等信息，在CBO情况下，如果不开动态采样，并且没有列的统计信息，则列的选择度会采用默认选择度，这样，有可能会带来问题

SQL*Plus: Release 11.2.0.1.0 Production on 星期日 9月 26 16:58:02 2010

Copyright (c) 1982, 2010, Oracle. All rights reserved.

SUNDOG315>conn test/test
已连接。
SUNDOG315>drop table t;

表已删除。

SUNDOG315>create table t(id integer,text varchar2(400));

表已创建。

SUNDOG315>insert into t select 1,object_name from dba_objects;

已创建55045行。

SUNDOG315>commit;

提交完成。

SUNDOG315>insert into t select 2,'aaa' from dba_objects where rownum=1;

已创建 1 行。

--id只有两个值1，2

SUNDOG315>select id,count(*) from t group by id;

ID COUNT(*)
---------- ----------
1 55045
2 1

SUNDOG315>alter session set optimizer_dynamic_sampling=0; --关闭动态采样

会话已更改。

SUNDOG315>create index t_idx on t(id);

索引已创建。

SUNDOG315>set autot trace exp stat
SUNDOG315>select * from t where id=2;

已选择55045行。


执行计划
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 215 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 215 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 54 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=2)

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7541 consistent gets
108 physical reads
0 redo size
1138015 bytes sent via SQL*Net to client
40774 bytes received via SQL*Net from client
3671 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
55045 rows processed

SUNDOG315>select * from t where id=1;

已选择55045行。


执行计划
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 215 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 215 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 54 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=1)

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7679 consistent gets
107 physical reads
0 redo size
2297656 bytes sent via SQL*Net to client
40774 bytes received via SQL*Net from client
3671 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
55045 rows processed

--没有统计信息的情况下，采用默认选择度，走了错误的索引

SUNDOG315>exec dbms_stats.gather_table_stats(user,'T',null,null,method_opt=>'for
columns size auto',cascade=>true);

PL/SQL 过程已成功完成。

SUNDOG315>select * from t where id=1;

已选择55045行。


执行计划
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 550 | 15400 | 56 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 550 | 15400 | 56 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 220 | | 54 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=1)

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
4023 consistent gets
0 physical reads
0 redo size
1572307 bytes sent via SQL*Net to client
40774 bytes received via SQL*Net from client
3671 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
55045 rows processed

SUNDOG315>select * from t where id=2;

执行计划
----------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 550 | 15400 | 56 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 550 | 15400 | 56 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 220 | | 54 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=2)


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
4023 consistent gets
0 physical reads
0 redo size
1572307 bytes sent via SQL*Net to client
40774 bytes received via SQL*Net from client
3671 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
55045 rows processed

--错误的收集方式，列的信息没有收集，依然使用默认选择度，选择了错误的执行路径

SUNDOG315>exec dbms_stats.gather_table_stats(user,'T',null,null,method_opt=>'for
all columns size 1',cascade=>true);

PL/SQL 过程已成功完成。

SUNDOG315>select * from t where id=1;

已选择55045行。


执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27523 | 752K| 103 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 27523 | 752K| 103 (1)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"=1)


统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3937 consistent gets
0 physical reads
0 redo size
1572220 bytes sent via SQL*Net to client
40775 bytes received via SQL*Net from client
3671 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
55045 rows processed

SUNDOG315>select * from t where id=2;

执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27523 | 752K| 103 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 27523 | 752K| 103 (1)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"=2)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
483 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

--虽然没有收集直方图，但是，收集了列的统计信息，发现NDV只有2，那么，选择度为50%，走全表扫描。但是，对于id=2，确实错误的执行计划

SUNDOG315>exec dbms_stats.gather_table_stats(user,'T',null,null,method_opt=>'for
all columns size auto',cascade=>true);

PL/SQL 过程已成功完成。

SUNDOG315>select * from t where id=2;

执行计划
----------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 28 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=2)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
483 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

--完整收集，包含直方图，那么对于id=2也可以获得正确的结果了

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19423/viewspace-1039005/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19423/viewspace-1039005/