For the parameter Optimizer_index_cost_adj

最新推荐文章于 2025-08-08 17:29:46 发布

henrybai

最新推荐文章于 2025-08-08 17:29:46 发布

阅读量573

点赞数

CC 4.0 BY-SA版权

文章标签： parameters express performance oracle system internet

本文链接：https://blog.youkuaiyun.com/henrybai/article/details/2529479

本文探讨了Oracle数据库中Optimizer_index_cost_adj参数的重要性及其对系统性能的影响。通过调整该参数至更合适的值（如10到30之间），可以显著提高某些OLTP系统的性能表现。文章还提供了一种确定最佳参数值的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

For the parameter Optimizer_index_cost_adj I find it is the most important parameter of all the other parameters, and the default setting of 100 is incorrect for most Oracle systems.
From the Internet I got to know:for some OLTP systems, re-setting this parameter to a smaller value (between 10- to 30) may result in huge performance gains!
And
The parameter can be set from 1 to 10000，
The default is 100.
There is a formula to express Optimizer_index_cost_adj;
Optimizer_index_cost_adj*(index scan cost)==Full Scan cost.
I have tested it in my way .
I got the way to find best value should be set for Optimizer_index_cost_adj.

col c1 heading 'Average Waits for|Full Scan Read I/O' format 9999.999
col c2 heading 'Average Waits for|Index Read I/O' format 9999.999
col c3 heading 'Percent of| I/O Waits|for scattered|Full Scans' format 999.999
col c4 heading 'Percent of| I/O Waits|for sequential|Index Scans' format 999.999
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999
select
   sum(a.time_waited_micro)/sum(a.total_waits)/1000000 c1,
   sum(b.time_waited_micro)/sum(b.total_waits)/1000000 c2,
   (
      sum(a.total_waits) /
      sum(a.total_waits + b.total_waits)
   ) * 100 c3,
   (
      sum(b.total_waits) /
      sum(a.total_waits + b.total_waits)
   ) * 100 c4,
(
      sum(b.time_waited_micro) /
      sum(b.total_waits)) /
      (sum(a.time_waited_micro)/sum(a.total_waits)
   ) * 100 c5
from
   dba_hist_system_event a,
   dba_hist_system_event b
where
   a.snap_id = b.snap_id
and
   a.event_name = 'db file scattered read'
and
   b.event_name = 'db file sequential read';

                                                                   Starting
                                                                      Value
                                                                        for
                                        Percent of     Percent of optimizer
                                         I/O Waits      I/O Waits     index
Average Waits for Average Waits for for scattered for sequential      cost
Full Scan Read I/O    Index Read I/O    Full Scans    Index Scans       adj
------------------ ----------------- ------------- -------------- ---------
              .011              .005        56.452         43.548        41

Luckily ,The parameter can be modified without restart database.

select isses_modifiable,issys_modifiable
from v$parameter
where name='optimizer_index_cost_adj';

ISSES ISSYS_MOD
----- ---------
TRUE IMMEDIATE

I wish ORACLE could provide more decent default value for it:)))