SQL> select round((200-2)/(75349-2),5) selectivity from dual;
io_cost=(blevel+(leaf_blocks+clustering_factor)*selectivity)*
(optimizer_index_cost_adj/100)
SQL> show parameter optimizer_index_cost_adj
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 100
optimizer_index_cost_adj=100
SQL> select (1+ceil(161*(round((200-2)/(75349-2),5)))+ceil(1665*(round((200-2)/(75349-2),5))))*(100/100) cost
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)
当把optimizer_index_cost_adj设置为50时
SQL> alter session set optimizer_index_cost_adj=50;
SQL> show parameter optimizer_index_cost_adj;
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 50
optimizer_index_cost_adj=50
SQL> select (1+ceil(161*(round((200-2)/(75349-2),5)))+ceil(1665*(round((200-2)/(75349-2),5))))*(50/100) cost from
我们计算出来是3.5四舍五入就是4与下面oracle计算的是一样
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 | 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后索引范围扫描操作选择索引的变化.
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
10 order by dbms_random.value;
SQL> create index idx_val2 on test (val2);
SQL> create index idx_val3 on test(val3);
SQL> show parameter optimizer_index_cost_adj
------------------------------------ ----------- ------------------------------
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 |
------------------------------------------------
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|
------------------------------------------------
为了避免这种不稳定性,建议不要设置optimizer_index_cost_adj为一个很小的值.该参数是一个动态参数
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26015009/viewspace-763123/,如需转载,请注明出处,否则将追究法律责任。