ITPub博客

首页 > Linux操作系统 > Linux操作系统 > OPTIMIZER_INDEX_CACHING和OPTIMIZER_INDEX_COST_ADJ

OPTIMIZER_INDEX_CACHING和OPTIMIZER_INDEX_COST_ADJ

原创 Linux操作系统 作者:shuangoracle 时间:2012-07-22 22:28:13 0 删除 编辑
OPTIMIZER_INDEX_CACHING

PropertyDescription
Parameter typeInteger
Default value0
ModifiableALTER SESSION, ALTER SYSTEM
Range of values0 to 100

OPTIMIZER_INDEX_CACHING lets you adjust the behavior. of cost-based optimization to favor nested loops joins and IN-list iterators.

The cost of executing an index using an IN-list iterator or of executing a nested loops join when an index is used to access the inner table depends on the caching of that index in the buffer cache. The amount of caching depends on factors that the optimizer cannot predict, such as the load on the system and the block access patterns of different users.

You can modify the optimizer's assumptions about index caching for nested loops joins and IN-list iterators by setting this parameter to a value between 0 and 100 to indicate the percentage of the index blocks the optimizer should assume are in the cache. Setting this parameter to a higher value makes nested loops joins and IN-list iterators look less expensive to the optimizer. As a result, it will be more likely to pick nested loops joins over hash or sort-merge joins and to pick indexes using IN-list iterators over other indexes or full table scans. The default for this parameter is 0, which results in default optimizer behavior.


OPTIMIZER_INDEX_COST_ADJ

PropertyDescription
Parameter typeInteger
Default value100
ModifiableALTER SESSION, ALTER SYSTEM
Range of values1 to 10000

OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior. for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.

The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.

optimizer_index_caching参数告诉oracle能够在缓冲区中找到索引块的平均百分比。0认为缓冲区没有索引块,也就意味着通过索引访问数据将产生物理读。100认为全在缓冲区中。可能希望用接近于高速缓存命中率的某个值为开始调整,看对系统的作用如何。

optimizer_index_cost_adj
用来告诉oracle高速缓存多少表数据的参数。这个数越小,表访问单个块的成本就越低;理解它的办法是:想象这个数反映执行多块IO(全表扫描)的成本与执行单个IO(索引读取)的成本。保持为100,则多块IO与单块IO成本相同。设为50优化程序认为访问单块IO的成本为多块IO的一半。

小结:
这两个参数的设置不会使计划执行得更快,它只是影响选择哪个计划。
一般而言,默认的参数值很可能对系统不合适
对多数系统而言考虑如下极端情况:
optimizer_index_caching = 0 optimizer_index_cost_adj = 100 的默认值,一般时候数据仓库报表系统。
optimizer_index_caching = 90 optimizer_index_cost_adj = 25一般时候事务处理/OLTP系统。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24496749/viewspace-736305/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 挪窝窝
请登录后发表评论 登录
全部评论

注册时间:2010-08-25

  • 博文量
    84
  • 访问量
    206946