oracle的sql调优工具,Oracle的sql调优工具

如何使用Oracle的sql调优工具

在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 Optimizer=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 Optimizer=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 Optimizer=CHOOSE (Cost=2 Card=2 Bytes=40)