oracle index_stats,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中的数据字典信息被刷新，相关的执行计划会立即改变。