OPTIMIZER_INDEX_COST_ADJ与成本计算

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 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to:Oracle9i Enterprise Edition Release 9.2.0.3.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.3.0 - Production SQL> show parameter optimizer_index_cost_adj NAME TYPE VALUE------------------------------------ ----------- ------------------------------optimizer_index_cost_adj integer 100SQL>

创建测试表:

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 traceonlySQL> 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 offSQL> 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

2004年6月28日 星期一 17:03

If you have any question,please mail to eygle@itpub.net .

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

一条简单的sql在11g和12c中的不同(r5笔记第2天)

今天在查看awr报告的时候,有一句很简单的sql语句引起了我的注意,因为它排在SQL Order by Reads的第2位。 Physical ReadsExe...

36340
来自专栏杨建荣的学习笔记

通过使用hint unnest调优sql语句(r4笔记第38天)

生产环境中有一条sql语句通过sql_monitor看到执行的时间实在是太惊人了,竟然达到了13个小时,而且还没有执行完。 SessionAPPC (20015...

32280
来自专栏乐沙弥的世界

Oracle 监控索引的使用率

    Oracle提供了索引监控特性来判断索引是否被使用。在Oracle 10g中,收集统计信息会使得索引被监控,在Oracle 11g中该现象不复存在。尽管...

20930
来自专栏杨建荣的学习笔记

sql_profile的使用(一) (r2笔记29天)

今天看了老熊关于sql_profile的讲解,受益匪浅,自己在本机也做了一通,感觉好记性不如烂笔头还是得多总结总测试才能真正理解。 准备的数据如下,创建两个表,...

28990
来自专栏峰会SaaS大佬云集

Oracle 数据库入门之----------------------,多行函数

  SUM(SAL)                                                                      ...

11900
来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第四篇(r2笔记41天)

生产中有一条sql语句消耗了大量的cpu资源,执行时间在18秒左右, Session:PRODBUSER (1560:61133)SQL ID:1hg2wcua...

21750
来自专栏数据库新发现

如何从结果集中获得随机结果

全表扫描(Full table Scan) 全表扫描返回表中所有的记录。 执行全表扫描,Oracle读表中的所有记录,考查每一行是否满足WHERE条件。Orac...

10520
来自专栏乐沙弥的世界

当心外部连接中的ON子句

       在SQL tuning中,不良写法导致SQL执行效率比比皆是。最近的SQL tuning中一个外部连接写法不当导致过SQL执行时间超过15分钟左右...

17340
来自专栏杨建荣的学习笔记

执行计划中的COLLECTION ITERATOR PICKLER FETCH导致的性能问题 (r5笔记第49天)

今天开发的同事找到我,让我评估一个sql语句。因为这条语句被应用监控组给抓取出来了,需要尽快进行性能调优。 sql语句比较长,是由几个Union连接起来的子查询...

39250
来自专栏杨建荣的学习笔记

关于查询转换的一些简单分析(二) (r3笔记第68天)

关于查询转换的作用,在之前的一篇博文里面也大概说了视图合并和子查询嵌套的部分。http://blog.itpub.net/23718752/viewspace-...

291110

扫码关注云+社区

领取腾讯云代金券