ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [zt] OPTIMIZER_INDEX_CACHING和OPTIMIZER_INDEX_COST_ADJ

[zt] OPTIMIZER_INDEX_CACHING和OPTIMIZER_INDEX_COST_ADJ

原创 Linux操作系统 作者:tolywang 时间:2009-06-12 18:43:52 0 删除 编辑

这两个参数是在oracle 9i对执行计划的优化开始发挥影响。


OPTIMIZER_INDEX_CACHING
该初始化参数表示一个百分比,0%~99%,缺省值0 ,对cbo来说,意味着0%的数据块(使用索引访问)可以在oracle‘s SGA的buffer cache中发现。即所有的对索引的访问都将需要物理读(每一个对buffer cache的逻辑读都产生一个对I/o子系统的物理读),也可以看作是:对buffer cache 0%的hit ratio。该参数只影响CBO计算访问索引块时候的成本,和涉及的表无关。
This parameter applies only to the CBO’s calculations of accesses for blocks in an index, not for the blocks in the table related to the index.

OPTIMIZER_INDEX_COST_ADJ
该初始化参数也表示一个百分比,0~10000,表示索引访问的i/o开销相对于全表扫描的i/o开销。缺省值100 ,对cbo来说,表示索引访问的开销和全表扫描是等效的。(索引访问花费的时间和全表扫描花费的时间几乎是相等的。)
The default value of 100 indicates to the cost-based optimizer that indexed access is 100% as costly (i.e., equally costly) as FULL table scan access.

OPTIMIZER_INDEX_CACHING 应该设成 90 ,对于大多数的OLTP系统 OPTIMIZER_INDEX_COST_ADJ 的范围应该在10到50之间;而对于数据仓库或其他DSS, 就需要小心的设置成50,下面由一个好方法用来确定如何设置该参数:
a valid value for OPTIMIZER_INDEX_COST_ADJ can easily be retrieved from the Oracle database itself. The answers lie in the V$SYSTEM_EVENT view, in the column AVERAGE_WAIT.
After the database has been up and running normally for a sufficient period of time (i.e., a couple hours or more), perform. the following query:
SELECT EVENT,AVERAGE_WAIT FROM V$SYSTEM_EVENT
WHERE EVENT LIKE ‘db file s%’;
This query will retrieve information on the two I/O wait events db file scattered reads (a.k.a. FULL table scans) and db file sequential reads (a.k.a. indexed scans). The AVERAGE_WAIT column contains the average timing, in 1/100ths of a second, of these events:
EVENT AVERAGE_WAITS
========================= ==============
db file sequential reads .33178629
db file scattered reads 2.190087
In this example, indexed scan I/O requests takes only 15% as long as each FULL table scan I/O request. So, set OPTIMIZER_INDEX_COST_ADJ to 15.(SQL>select .33178629/2.190087 from dual;

.33178629/2.190087
------------------
.151494571)


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

请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13204818