dbms_stats是oracle用来代替原有的analyze功能的一个包，与analyze相比dbms_stats具有很多优势，比如并行，比如分区信息统计等，但是dbms_stats再分析index的时候处理方式并不是太理想，dbms_stats分析index时将会只统计leaf_blocks为当前有数据的leaf block，而analyze则会统计为所有曾经被使用过的leaf block number，很显然dbms_stats的统计结果会使index fast full scan的成本被严重低估，在某些情况下会错误得选择index fast full scan做为执行路径。下面来看一个例子：
先清空原来的表
SQL 10G>truncate table t1;
Table truncated.
插入数据
SQL 10G>insert into t1 select
2 rownum id,
3 trunc(100 * dbms_random.normal) val,
4 rpad('x',100) padding
5 from
6 all_objects
7 where
8 rownum <= 10000
9 ;
10000 rows created.
SQL 10G>commit;
Commit complete.
创建索引，并限制pctfree为99，模拟大索引的产生
SQL 10G>create index ind_t1 on t1(id) pctfree 99;
Index created.
使用analyze分析索引
SQL 10G>ANALYZE INDEX IND_T1 COMPUTE STATISTICS;
Index analyzed.
SQL 10G>SELECT INDEX_NAME,BLEVEL,LEAF_BLOCKS FROM user_indexes where table_name='T1';
INDEX_NAME BLEVEL LEAF_BLOCKS
------------------------------ ---------- -----------
IND_T1 2 10000
再使用dbms_stats分析，可以看到在这个时候基本统计数据是相同的
SQL 10G>EXEC DBMS_STATS.GATHER_INDEX_STATS('TEST','IND_T1');
PL/SQL procedure successfully completed.
SQL 10G>SELECT INDEX_NAME,BLEVEL,LEAF_BLOCKS FROM user_indexes where table_name='T1';
INDEX_NAME BLEVEL LEAF_BLOCKS
------------------------------ ---------- -----------
IND_T1 2 10010
删除数据使表中只保留一条记录
SQL 10G>delete from t1 where rownum<10000;
9999 rows deleted.
SQL 10G>commit;
Commit complete.
再用analyze分析索引，可以发现leaf_blocks依然是10000
SQL 10G>ANALYZE INDEX IND_T1 COMPUTE STATISTICS;
Index analyzed.
SQL 10G>SELECT INDEX_NAME,BLEVEL,LEAF_BLOCKS FROM user_indexes where table_name='T1';
INDEX_NAME BLEVEL LEAF_BLOCKS
------------------------------ ---------- -----------
IND_T1 2 10000
看cost这一项显示index fast full scan的成本为2679，这是正确的
SQL 10G>set autotrace trace exp;
SQL 10G>select/*+ index_ffs(t1,ind_t1)*/ count(*) from t1;
Execution Plan
-------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2679 (19)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | INDEX FAST FULL SCAN| IND_T1 | 1 | 2679 (19)|
-------------------------------------------------------------
使用dbms_stats分析索引，leaf_blocks被统计为1，只统计了当前在用的leaf block
SQL 10G>EXEC DBMS_STATS.GATHER_INDEX_STATS('TEST','IND_T1');
PL/SQL procedure successfully completed.
SQL 10G>set autotrace off;
SQL 10G>SELECT INDEX_NAME,BLEVEL,LEAF_BLOCKS FROM user_indexes where table_name='T1';
INDEX_NAME BLEVEL LEAF_BLOCKS
------------------------------ ---------- -----------
IND_T1 2 1
看cost这一项显示index fast full scan的成本为1，这显然是出现了错误
SQL 10G>set autotrace trace;
SQL 10G>select/*+ index_ffs(t1,ind_t1)*/ count(*) from t1;
Execution Plan
-------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | INDEX FAST FULL SCAN| IND_T1 | 1 | 1 (0)|
-------------------------------------------------------------
再来看看它究竟需要读取多少个块，是不是cost＝1就够了
SQL 10G> ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
Session altered.
SQL 10G>select/*+ index_ffs(t1,ind_t1)*/ count(*) from t1;
Execution Plan
----------------------------------------------------------
-------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | INDEX FAST FULL SCAN| IND_T1 | 1 | 1 (0)|
-------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10035 consistent gets
10016 physical reads
0 redo size
411 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
显然，这里发生了10016个physical reads，cost＝1是远远不够的。不知道oracle会不会就这个问题有改进方案，大家拭目以待。
