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