| Profilo di haiOracle & StarcraftBlogElenchi |  Strumenti  Guida |
|
04 agosto dbms_stats和leaf_blocksdbms_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会不会就这个问题有改进方案，大家拭目以待。
Commenti (25)Per aggiungere un commento, accedi con il tuo Windows Live ID (se utilizzi Hotmail, Messenger o Xbox LIVE possiedi già un Windows Live ID). Accedi Non hai ancora un Windows Live ID? Registrati
RiferimentiL'URL di riferimento per questo intervento è: http://wzwanghai.spaces.live.com/blog/cns!56626E237AFBD116!328.trak Blog che fanno riferimento a questo intervento
|
|
|
