optimizer_index_cost_adj参数的作用

optimizer_index_cost_adj
这个初始化参数optimizer_index_cost_adj用于改变通过索引扫描来访问表数据的成本.参数的有效值从1到10000.
缺省值是100.如果这个参数的值大于100那么使用索引扫描的成本更昂贵因而会选择执行全表扫描.如果这个参数值
小于100那么使用索引扫描的成本更低.

为了理解这个参数对成本计算公式的影响.下面将说明一下关于索引范围扫描的成本计算公式

索引范围扫描有几个关键步骤:
1.访问索引的根块
2.通过分支块来定位包含第一个键的叶子块
3.对于满足搜索条件的每一个索引健进行以下操作;
抽取rowid引用的数据块
通过rowid访问数据块.

一个索引范围扫描所有执行的物理读取的次数等于定位包含第一个键的叶子块所要访问的分支块的个数(命名为blevel)
加上要扫描的叶子块的个数(命名为leaf_blocks)乘以操作的选择性,加上通过rowid要访问的数据块的个数
(命名为clustering_factor)乘以操作的选择性.另外还有考虑初始化参数optimizer_index_cost_adj的影响
计算公式大致如下:

io_cost=(blevel+(leaf_blocks+clustering_factor)*selectivity)*(optimizer_index_cost_adj/100)

下面进行测试(查询语句为select * from test where object_id<200)

create table test as select * from dba_objects;

create index idx_object_id on test(object_id);

analyze table test compute statistics;

SQL> select LEAF_BLOCKS,BLEVEL ,clustering_factor from user_indexes where index_name='IDX_OBJECT_ID';

LEAF_BLOCKS     BLEVEL CLUSTERING_FACTOR
----------- ---------- -----------------
        161          1              1665

SQL> select low_value,high_value from user_tab_col_statistics where table_name='TEST' and   column_name='OBJECT_ID';

LOW_VALUE                                                        HIGH_VALUE
---------------------------------------------------------------- ----------------------------------------------- -----------------
C103                                                             C3083632

SQL>
SQL> select utl_raw.cast_to_number('C3083632') high_value from dual;

HIGH_VALUE
----------
     75349
SQL> select utl_raw.cast_to_number('C103') low_value from dual;

 LOW_VALUE
----------
         2

其实列的最大值与最小值可以直接查询

SQL>select min(object_id),max(object_id) from test;

MIN(OBJECT_ID) MAX(OBJECT_ID)
-------------- --------------
2              75349
计算选择性
<谓词的选择性=(limit- low_value)/(high_vlaue-low_value)
limit就是查询条件的值
SQL>select round((200-2)/(75349-2),5) selectivity from dual;

SELECTIVITY
-----------
0.00263

因为io_cost的计算方法如下:

io_cost=(blevel+(leaf_blocks+clustering_factor)*selectivity)*(optimizer_index_cost_adj/100)
SQL> show parameter optimizer_index_cost_adj

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 100
optimizer_index_cost_adj=100
blevel=1;
leaf_blocks=161
clustering_factor=1665
selecttivity=0.00263

SQL>select (1+ceil(161*(round((200-2)/(75349-2),5)))+ceil(1665*(round((200-2)/(75349-2),5))))*(100/100) cost
from dual;

COST
----------
7

SQL>conn jy/jy@jy_201
已连接。
SQL>set autotrace trace explain
SQL> select  * from test where object_id<200;

执行计划
----------------------------------------------------------
Plan hash value: 985375477

---------------------------------------------------------------------------------------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |   191 | 19100 |     7   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST          |   191 | 19100 |     7   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_ID |   191 |       |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"<200)

可以看到总的成本也和我们计算出来的一样也是7
当把optimizer_index_cost_adj设置为50时

SQL>alter session set optimizer_index_cost_adj=50;

Session altered.

SQL> show parameter optimizer_index_cost_adj;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 50

optimizer_index_cost_adj=50
blevel=1;
leaf_blocks=161
clustering_factor=1665
selecttivity=0.00263

SQL>select (1+ceil(161*(round((200-2)/(75349-2),5)))+ceil(1665*(round((200-2)/(75349-2),5))))*(50/100) cost from
dual;

COST
----------
3.5

我们计算出来是3.5四舍五入就是4与下面oracle计算的是一样

SQL> set autotrace trace explain
SQL> select  * from test where object_id<200;

Execution Plan
----------------------------------------------------------
Plan hash value: 985375477

---------------------------------------------------------------------------------------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |   191 | 19100 |     4   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST          |   191 | 19100 |     4   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_ID |   191 |       |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"<200)

初始化参数optimizer_index_cost_adj会直接影响索引访问的成本.当这个参数设置的值比缺省值小时,
所有成本按比例减小.在有些情况下可能出现问题因为查询优化器对评估结果做了round操作.这意味着即使
一些索引的对象统计不同,它们可能会有相同的成本.如果一些成本是相同的,那么查询优化器将会根据索引
的名字来选择使用的索引.也就是按索引的第一个字母进行排序.这个问题用一个例子来说明.当改变初始化
参数optimizer_index_cost_adj后索引范围扫描操作选择索引的变化.

drop table test purge;

SQL> create table test
  2  as
  3  select rownum as id,
  4         round(dbms_random.normal*10000) as val1,
  5         100+round(ln(rownum/3.25+2)) as val2,
  6         100+round(ln(rownum/3.25+2)) as val3,
  7         dbms_random.string('p',250) as pad
  8  from all_objects
  9  where rownum< =1000
 10  order by dbms_random.value;

SQL> create index idx_val2 on test (val2);

Index created.

Elapsed: 00:00:00.18
SQL> create index idx_val3 on test(val3);

Index created.

Elapsed: 00:00:00.09

SQL> show parameter optimizer_index_cost_adj

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj             integer     100

SQL> SELECT * FROM test WHERE val2 = 111 AND val3 = 111;
------------------------------------------------
| Id | Operation                   | Name     |
------------------------------------------------
|  0 | SELECT STATEMENT            |          |
|* 1 |  TABLE ACCESS BY INDEX ROWID| TEST     |
|* 2 |   INDEX RANGE SCAN          | IDX_VAL3 |
------------------------------------------------
1 - filter("VAL2"=11)
2 - access("VAL3"=11)

SQL> ALTER SESSION SET optimizer_index_cost_adj = 10;
SQL> SELECT * FROM test WHERE val1 = 111 AND val2 = 111;
------------------------------------------------
| Id | Operation                   | Name    |
------------------------------------------------
|  0 | SELECT STATEMENT            |         |
|* 1 |  TABLE ACCESS BY INDEX ROWID| TEST    |
|* 2 |   INDEX RANGE SCAN          | IDX_VAL2|
------------------------------------------------
1 - filter("VAL3"=111)
2 - access("VAL2"=111)

为了避免这种不稳定性,建议不要设置optimizer_index_cost_adj为一个很小的值.该参数是一个动态参数
可以在实例及会话级别进行修改.

28 thoughts on “optimizer_index_cost_adj参数的作用

  1. My brother suggested I would possibly like this blog. He was entirely right.
    This post actually made my day. You can not consider simply
    how so much time I had spent for this info! Thank you!

  2. An interesting discussion is worth comment. I feel that you really should write even more on this topic, it may well not be a taboo topic but frequently persons are not sufficient to speak on such topics. To the next. Cheers
    [url=http://jordanscheapthi.blognet.me/]jordans cheap[/url]

  3. Thank you for article about optimizer_index_cost_adj参数的作用 | jingyong. Other web-site editor should take this website content as a benchmark because it has very clean and wonderful style and design, as well as the very valuable content. You are surely an expert in this topic! If you have a few seconds, I would appreciate it if you could check out my blog on vin lookup, thank you!

  4. Nice blog post about optimizer_index_cost_adj参数的作用 | jingyong. I appreciate you taking the time to write about this wonderful and insightful topic. Have a nice day and when you have time, check out my blog about car checker!

  5. We are a group of volunteers and opening a new scheme in our community. Your website offered us with valuable info to work on. You***217;ve done an impressive job and our entire community will be thankful to you. gw2 gold

  6. I***217;m just commenting to make you understand of the perfect encounter my friend***217;s girl had studying your web site. She figured out some things, which include what it is like to have an amazing giving style to make most people really easily completely grasp certain very confusing issues. You truly did more than my desires. I appreciate you for rendering such great, dependable, informative as well as fun tips on that topic to Kate. FFXIV Power Leveling

  7. One thing I would really like to say is always that before getting more personal computer memory, look into the machine in to which it could be installed. In the event the machine will be running Windows XP, for instance, the actual memory threshold is 3.25GB. The installation of over this would simply constitute a waste. Make sure that one***217;s mother board can handle the actual upgrade amount, as well. Thanks for your blog post. swtor Imperial Agent power leveling

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.