OPTIMIZER_INDEX_COST_ADJ与成本计算

« Oracle诊断案例-Sql_trace之一 | Blog首页 | 安装10g以后Oracle9i的DBCA无法启动问题之解决 »

OPTIMIZER_INDEX_COST_ADJ与成本计算

作者：eygle |【转载时请务必以超链接形式标明文章原始出处和作者信息及本声明】
链接：http://www.eygle.com/archives/2004/06/optimizer_index_cost_adj.html

站内相关文章|Related Articles

如何对时间进行简单加减运算

并行查询并行度Degree与instances 设置

关于PARALLEL_MAX_SERVERS参数的设置

使用REF CURSOR处理Oracle的结果集

Oracle Peeking绑定变量的控制

OPTIMIZER_INDEX_COST_ADJ
这个初始化参数代表一个百分比,取值范围在1到10000之间.
该参数表示索引扫描和全表扫描成本的比较。缺省值100表示索引扫描成本等价转换与全表扫描成本。

这些参数对于CBO的执行具有重大影响，其缺省值对于数据库来说通常需要调整。
一般来说对于OPTIMIZER_INDEX_CACHING可以设置为90左右
对于大多数OLTP系统，OPTIMIZER_INDEX_COST_ADJ可以设置在10到50之间。对于数据仓库和DSS系统，
可能不能简单的把OPTIMIZER_INDEX_COST_ADJ设置为50，通常我们需要反复调整取得一个合理值.
更为具体的可以根据统计信息,db file scattered reads/db file sequential reads来计算.

本文通过实验对该参数的使用作出探讨和说明.

我们看到optimizer_index_cost_adj的缺省值为100.

[oracle@jumper udump]$ sqlplus eygle/eygle

SQL*Plus: Release 9.2.0.3.0 - Production on Mon Jun 28 17:11:15 2004

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

SQL> show parameter optimizer_index_cost_adj

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 100
SQL>

创建测试表:

SQL> create table t as select * from dba_objects;

Table created.

SQL> create index ind_owner on t(owner);

Index created.

SQL> analyze table t compute statistics;

Table analyzed.

我们分别观察一下全表扫描和索引访问的成本:

SQL> set autotrace traceonly

SQL> select * from t where owner='EYGLE';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=476 Bytes=36652)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=14 Card=476 Bytes=36652)

SQL> select /*+ index(t ind_owner) */ * from t where owner='EYGLE';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=476 Bytes=36652)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=16 Card=476 Bytes=36652)
2 1 INDEX (RANGE SCAN) OF 'IND_OWNER' (NON-UNIQUE) (Cost=2 Card=476)

Oracle在选择不同的访问路径时，会对全表扫描和索引扫描进行比较评估.

在比较的时候，Oracle会把索引扫描的成本转换为全表扫描的成本，和全表扫描的COST进行比较.这个转换需要一个转换因子.
就是optimizer_index_cost_adj:

optimizer_index_cost_adj * (Index Scan Cost) = 等价的 Full Scan Cost

这个等价的 Full Scan Cost 就是来和全表扫描成本进行比较的.

而这个转换因子的临界值实际上就是Full Scan Cost 和 Index Scan Cost的比值.

即:

optimizer_index_cost_adj = Full Scan Cost / Index Scan Cost

SQL> set autotrace off
SQL> select (14/16)*100 from dual;

(14/16)*100
-----------
87.5

1 row selected.

我们通过调整optimizer_index_cost_adj来看一下执行计划的变化:

SQL> set autotrace traceonly

SQL> alter session set optimizer_index_cost_adj = 87;

Session altered.

SQL> select * from t where owner='EYGLE';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=476 Bytes=36652)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=14 Card=476 Bytes=36652)
2 1 INDEX (RANGE SCAN) OF 'IND_OWNER' (NON-UNIQUE) (Cost=2 Card=476)

此时使用索引成本较低.等价全表扫描成本为:

87% * (Index Scan Cost) < Full Scan Cost

此时Oracle选择了索引.

SQL> alter session set optimizer_index_cost_adj = 88;

Session altered.

SQL> select * from t where owner='EYGLE';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=476 Bytes=36652)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=14 Card=476 Bytes=36652)

此时使用索引成本较高.等价全表扫描成本为:

88% * (Index Scan Cost) > Full Scan Cost

所以Oracle选择了全表扫描.

参考文档:

http://www.evdbt.com/SearchIntelligenceCBO.doc

-----
这篇【OPTIMIZER_INDEX_COST_ADJ与成本计算】来自 www.eygle.com | CSDN技术网摘| del.icio.us|365Key

By eygle on 2004-06-28 21:01 | Comments (0) | Posted to SQL.PLSQL | Edit |Pageviews:

相关文章	随机文章
如何对时间进行简单加减运算并行查询并行度Degree与instances 设置关于PARALLEL_MAX_SERVERS参数的设置使用REF CURSOR处理Oracle的结果集 Oracle Peeking绑定变量的控制	IT168约以100万美元收购ITPUB Google的新玩具:Webaccelerator 10个想法备份的控制文件和新的数据文件 Gregarius与Lilina，我的Rss历程...

网上相关主题:

www.eygle.com

OPTIMIZER_INDEX_COST_ADJ与成本计算

留言 (0)

发表留言: