Oracle & Starcraft

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会不会就这个问题有改进方案，大家拭目以待。