optimizer_index_cost_adj引數的作用

eric0435發表於2013-06-05
Index
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就是查詢條件的值
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為一個很小的值.該引數是一個動態引數
可以在例項及會話級別進行修改.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-763123/，如需轉載，請註明出處，否則將追究法律責任。

相關文章