轉自[@more@]

掃描成本和OPTIMIZER_INDEX_COST_ADJ

我們知道，在CBO模式下，Oracle會計算各個訪問路徑的代價，採用最小代價的訪問路徑作為語句的執行計劃。而對於索引的訪問代價的計算，需要根據一個系統引數OPTIMIZER_INDEX_COST_ADJ來轉換為與全表掃描代價等價的一個值。這是什麼意思呢？我們先稍微解釋一下這個引數：OPTIMIZER_INDEX_COST_ADJ。它的值是一個百分比，預設是100，取值範圍是1～10000。當估算索引掃描代價時，會將索引的原始代價值乘以這個百分比，將換算後的值作為與全表掃描代價比較的值。也就是說，當這個值為100時，計算出的索引掃描代價就是它的原始代價：

COST_COM = COST_ORG * OPTIMIZER_INDEX_COST_ADJ/100

看以下例子：

SQL> create table T_PEEKING (a NUMBER, b char(1), c char(2000));

Table created.

SQL>

SQL> create index T_PEEKING_IDX1 on T_PEEKING(b);

Index created.

SQL> begin

  2    for i in 1..1000 loop

  3      insert into T_PEEKING values (i, 'A', i);

  4    end loop;

  5

  6    insert into T_PEEKING values (1001, 'B', 1001);

  7    insert into T_PEEKING values (1002, 'B', 1002);

  8    insert into T_PEEKING values (1003, 'C', 1003);

  9

 10    commit;

 11  end;

 12  /

PL/SQL procedure successfully completed.

注意，我們給索引欄位B插入的值中只有3個distinct值，記錄數是1003，它的集的勢很高（1003/3）=334。關於集的勢的計算，可以參考我的另外一篇文件。

SQL>

SQL> analyze table T_PEEKING compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL>

我們看下索引掃描的代價是多少：

SQL> show parameter OPTIMIZER_INDEX_COST_ADJ

NAME                                 TYPE        VALUE

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

optimizer_index_cost_adj             integer     100

SQL> delete from plan_table;

0 rows deleted.

SQL>

SQL> explain plan for select /*+index(a T_PEEKING_IDX1)*/ * from T_PEEKING a where b = :V;

Explained.

SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||

  2         object_name||' '||decode(id, 0, 'Cost='||position) "Query

  3  Plan_Table"

  4      from plan_table

  5      start with id = 0

  6      connect by prior id = parent_id

  7  ;

Query

Plan_Table

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

SELECT STATEMENT   Cost=113

  TABLE ACCESS BY INDEX ROWID T_PEEKING

    INDEX RANGE SCAN T_PEEKING_IDX1

SQL>

再看全表掃描的代價是多少：

SQL> delete from plan_table;

3 rows deleted.

SQL>

SQL> explain plan for select /*+full(a)*/ * from T_PEEKING a where b = :V;

Explained.

SQL>

SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||

  2         object_name||' '||decode(id, 0, 'Cost='||position) "Query

  3  Plan_Table"

  4      from plan_table

  5      start with id = 0

  6      connect by prior id = parent_id

  7  ;

Query

Plan_Table

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

SELECT STATEMENT   Cost=75

  TABLE ACCESS FULL T_PEEKING

SQL>

這時，我們可以計算得出讓最佳化器使用索引（無提示強制）的OPTIMIZER_INDEX_COST_ADJ值應該 < ROUND(COST_FTS/COST_IDX*100) = ROUND(75/113*100) = 66，而大於66則會使用全表掃描：

SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=67;

System altered.

SQL>

SQL> delete from plan_table;

2 rows deleted.

SQL>

SQL> explain plan for select * from T_PEEKING a where b = :V;

Explained.

SQL>

SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||

  2         object_name||' '||decode(id, 0, 'Cost='||position) "Query

  3  Plan_Table"

  4      from plan_table

  5      start with id = 0

  6      connect by prior id = parent_id;

Query

Plan_Table

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

SELECT STATEMENT   Cost=75

  TABLE ACCESS FULL T_PEEKING

SQL>

SQL>

SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=66;

System altered.

SQL>

SQL> delete from plan_table;

2 rows deleted.

SQL>

SQL> explain plan for select * from T_PEEKING a where b = :V;

Explained.

SQL>

SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||

  2         object_name||' '||decode(id, 0, 'Cost='||position) "Query

  3  Plan_Table"

  4      from plan_table

  5      start with id = 0

  6      connect by prior id = parent_id;

Query

Plan_Table

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

SELECT STATEMENT   Cost=75

  TABLE ACCESS BY INDEX ROWID T_PEEKING

    INDEX RANGE SCAN T_PEEKING_IDX1

可以看出，在使用繫結變數時，引數OPTIMIZER_INDEX_COST_ADJ對於是否選擇索引會有重要的影響。

這裡我們暫且不討論索引掃描的原始成本是如何計算得出的。但是有一點很重要，在使用繫結變數時，計算出的成本是平均成本。在我們上面的例子中，欄位B的值只有3個："A"、"B"、"C"，其中A最多，1003行中有1000行。因此，在索引上掃描值為A記錄的成本為1000/1003 * 索引全掃描成本 ≈索引全掃描成本，我們看下它的成本是多少：

SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=100;

System altered.

SQL>

SQL> delete from plan_table;

2 rows deleted.

SQL>

SQL> explain plan for select /*+index(a T_PEEKING_IDX1)*/* from T_PEEKING a where b = 'A';

Explained.

SQL>

SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||

  2         object_name||' '||decode(id, 0, 'Cost='||position) "Query

  3  Plan_Table"

  4      from plan_table

  5      start with id = 0

  6      connect by prior id = parent_id;

Query

Plan_Table

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

SELECT STATEMENT   Cost=336

  TABLE ACCESS BY INDEX ROWID T_PEEKING

    INDEX RANGE SCAN T_PEEKING_IDX1

可以看到，它的成本是336。因此索引的平均成本是(336 * 1003/1000) / 3 ≈ 113，也就是使用繫結變數使的成本。而掃描其它兩個值"B"和"A"時代價就非常小。

SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=100;

System altered.

SQL>

SQL> delete from plan_table;

3 rows deleted.

SQL>

SQL> explain plan for select /*+index(a T_PEEKING_IDX1)*/* from T_PEEKING a where b = 'B';

Explained.

SQL>

SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||

  2         object_name||' '||decode(id, 0, 'Cost='||position) "Query

  3  Plan_Table"

  4      from plan_table

  5      start with id = 0

  6      connect by prior id = parent_id;

Query

Plan_Table

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

SELECT STATEMENT   Cost=2

  TABLE ACCESS BY INDEX ROWID T_PEEKING

    INDEX RANGE SCAN T_PEEKING_IDX1

因為計算的成本是平均成本（相對實際掃描某個值的成本，平均成本更接近全表掃描成本），因此在建立查詢計劃時，使用繫結變數將更加容易受到引數影響，特別是上面的這種情況（即索引欄位的集的勢非常高時）下，平均代價與實際掃描某個值代價相差非常遠。這種情況下，OPTIMIZER_INDEX_COST_ADJ對不使用繫結變數查詢影響就非常小（因為索引代價不是比全表掃描成本大很多就是小很多），不管掃描哪個值，不使用繫結變數將更加容易選擇到合理的查詢計劃。

繫結變數窺視

在瞭解了引數的作用後。再瞭解一個對查詢計劃，特別是使用繫結變數時會產生重大影響的特性：繫結變數窺視（Bind Variables Peeking）。

繫結變數窺視是9i以後的一個新特性。它使CBO最佳化器在計算訪問代價時，將繫結變數傳入的值考慮進去，從而計算出更合理的成本（否則，將會計算平均成本）。看下面例子：

SQL> conn sys/sys as sysdba

Connected.

SQL>

SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=60;

System altered.

SQL> analyze table T_PEEKING compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL>

SQL> set autot trace

SQL>

SQL> alter session set sql_trace = true;

Session altered.

SQL>

SQL> var v char(1)

SQL>

SQL> exec :v := 'A';

PL/SQL procedure successfully completed.

SQL>

SQL> select * from T_PEEKING a where b = :V;