Oracle9i中使用DBMS_STATS.DELETE_INDEX_STATS删除索引的统计

在Oracle9i中使用DBMS_STATS.DELETE_INDEX_STATS删除索引的统计后不能立刻对执行计划产生影响 （1）在scott.dept表的dname列有索引IDX_DEPT_DNAME，但没有对其进行分析统计 CREATE INDEX SCOTT.IDX_DEPT_DNAME ON SCOTT.DEPT (DNAME) TABLESPACE users; ORA9I> select BLEVEL,DISTINCT_KEYS,LAST_ANALYZED   2  from dba_indexes where INDEX_NAME='IDX_DEPT_DNAME';     BLEVEL DISTINCT_KEYS LAST_ANAL                                              ---------- ------------- ---------                                                                                                                              ORA9I> SET AUTOTRACE TRACEONLY EXP ORA9I> select * from scott.dept where dname='ACCOUNTING'; Execution Plan ----------------------------------------------------------                         0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=2 Bytes=40)               1    0   TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=2 Bytes=40)              此时，该查询没有使用IDX_DEPT_DNAME索引 （2）分析索引 ORA9I> BEGIN   2    SYS.DBMS_STATS.GATHER_INDEX_STATS (   3        OwnName        => 'SCOTT' ,IndName        => 'IDX_DEPT_DNAME' );   4  END;   5  / PL/SQL procedure successfully completed. ORA9I>  SET AUTOTRACE OFF ORA9I> select BLEVEL,DISTINCT_KEYS,LAST_ANALYZED   2  from dba_indexes where INDEX_NAME='IDX_DEPT_DNAME';     BLEVEL DISTINCT_KEYS LAST_ANAL                                              ---------- ------------- ---------                                                       0             4 15-JUN-05                                              ORA9I> SET AUTOTRACE TRACEONLY EXP ORA9I> select * from scott.dept where dname='ACCOUNTING'; Execution Plan ----------------------------------------------------------                         0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=2 Bytes=40)               1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=2 Card=2 Byt                    es=40)                                                                                                                                                   2    1     INDEX (RANGE SCAN) OF 'IDX_DEPT_DNAME' (NON-UNIQUE) (Cos                    t=1 Card=2)                                                                                                                                           此时，该查询使用IDX_DEPT_DNAME索引 （3）使用SYS.DBMS_STATS.DELETE_INDEX_STATS删除统计值 ORA9I> BEGIN   2    SYS.DBMS_STATS.DELETE_INDEX_STATS (   3        OwnName        => 'SCOTT'   ,IndName        => 'IDX_DEPT_DNAME');   4  END;   5  / PL/SQL procedure successfully completed. ORA9I> select * from scott.dept where dname='ACCOUNTING'; Execution Plan ----------------------------------------------------------                         0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=2 Bytes=40)               1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=2 Card=2 Byt                    es=40)                                                                                                                                                   2    1     INDEX (RANGE SCAN) OF 'IDX_DEPT_DNAME' (NON-UNIQUE) (Cos                    t=1 Card=2)                                                                                                                                             ORA9I> SET AUTOTRACE OFF ORA9I> select BLEVEL,DISTINCT_KEYS,LAST_ANALYZED   2  from dba_indexes where INDEX_NAME='IDX_DEPT_DNAME';     BLEVEL DISTINCT_KEYS LAST_ANAL                                              ---------- ------------- ---------                                                                                                                              此时，统计值虽然已经没有，但查询仍然使用IDX_DEPT_DNAME索引，分析可能的原因是shard pool中的字典信息(data dictionary cache)没有更新 （4）清空shared pool ORA9I>  ALTER SYSTEM FLUSH SHARED_POOL; System altered. ORA9I> SET AUTOTRACE TRACEONLY EXP ORA9I> select * from scott.dept where dname='ACCOUNTING'; Execution Plan ----------------------------------------------------------                         0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=2 Bytes=40)               1    0   TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=2 Bytes=40)              shared pool清空后，执行计划改变，不再使用索引 （5）使用Analyze命令删除索引统计值 ORA9I> BEGIN   2    SYS.DBMS_STATS.GATHER_INDEX_STATS (   3        OwnName        => 'SCOTT' ,IndName        => 'IDX_DEPT_DNAME' );   4  END;   5  / PL/SQL procedure successfully completed. ORA9I> SET AUTOTRACE OFF ORA9I> select BLEVEL,DISTINCT_KEYS,LAST_ANALYZED   2  from dba_indexes where INDEX_NAME='IDX_DEPT_DNAME';     BLEVEL DISTINCT_KEYS LAST_ANAL                                              ---------- ------------- ---------                                                       0             4 15-JUN-05                                              ORA9I> SET AUTOTRACE TRACEONLY EXP ORA9I> select * from scott.dept where dname='ACCOUNTING'; Execution Plan ----------------------------------------------------------                         0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=2 Bytes=40)               1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=2 Card=2 Byt                    es=40)                                                                                                                                                   2    1     INDEX (RANGE SCAN) OF 'IDX_DEPT_DNAME' (NON-UNIQUE) (Cos                    t=1 Card=2)                                                                                                                                             ORA9I> Analyze Index SCOTT.IDX_DEPT_DNAME   2  Delete Statistics; Index analyzed. ORA9I> select * from scott.dept where dname='ACCOUNTING'; Execution Plan ----------------------------------------------------------                         0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=2 Bytes=40)               1    0   TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=2 Bytes=40)                ORA9I> SET AUTOTRACE OFF ORA9I> select BLEVEL,DISTINCT_KEYS,LAST_ANALYZED   2  from dba_indexes where INDEX_NAME='IDX_DEPT_DNAME';     BLEVEL DISTINCT_KEYS LAST_ANAL                                              ---------- ------------- ---------                                                              可见，使用Analyze删除索引统计值后，字典缓冲中的数据也被更新，执行计划立刻改变                                                                （5）在10g（10.1.0.3.0 ）中 CREATE INDEX SCOTT.IDX_DEPT_DNAME ON SCOTT.DEPT (DNAME) TABLESPACE users; ORA10G> set autotrace off ORA10G> select BLEVEL,DISTINCT_KEYS,LAST_ANALYZED   2  from dba_indexes where INDEX_NAME='IDX_DEPT_DNAME';     BLEVEL DISTINCT_KEYS LAST_ANAL ---------- ------------- --------- ORA10G> set autotrace traceonly exp ORA10G> select * from scott.dept where dname='ACCOUNTING'; Execution Plan ----------------------------------------------------------    0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=3 Card=1 Bytes=20)    1    0   TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=1 Bytes           =20) ORA10G> BEGIN   2    SYS.DBMS_STATS.GATHER_INDEX_STATS (   3        OwnName        => 'SCOTT' ,IndName        => 'IDX_DEPT_DNAME' );   4  END;   5  / PL/SQL procedure successfully completed. ORA10G> select * from scott.dept where dname='ACCOUNTING'; Execution Plan ----------------------------------------------------------    0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=2 Card=1 Bytes=20)    1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=2 Ca           rd=1 Bytes=20)    2    1     INDEX (RANGE SCAN) OF 'IDX_DEPT_DNAME' (INDEX) (Cost=1 C           ard=1)   ORA10G> BEGIN   2    SYS.DBMS_STATS.DELETE_INDEX_STATS (   3        OwnName        => 'SCOTT'   ,IndName        => 'IDX_DEPT_DNAME');   4  END;   5  / PL/SQL procedure successfully completed. ORA10G> select * from scott.dept where dname='ACCOUNTING'; Execution Plan ----------------------------------------------------------    0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=3 Card=1 Bytes=20)    1    0   TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=1 Bytes           =20) 可见，在Oracle10g中，已解决了上述问题，通过DBMS_STATS.DELETE_INDEX_STATS删除索引统计值后，shared pool中的数据字典信息被刷新，相关的执行计划会立即改变。

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

转载于:http://blog.itpub.net/13940/viewspace-612983/