引數Optimizer_index_cost_adj 對執行計劃的影響

oracle_kai發表於2008-03-14
Index

引數Optimizer_index_cost_adj對執行計劃的影響

在基於cbo的優化器模式下面，oracle會把sql語句的各種執行計劃的成本做比較，取其最小的作為其最優執行計劃，同時，在決定是用index scan 還是full table scan 訪問一個表的時候，oracle會把index scan的執行成本轉化為fts的成本，然後再根據其成本大小來決定執行計劃，這個index scan 和full table scan 的比值即為 optimizer_index_cost_adj，下面的例子具體說明了該引數的不同取值對執行計劃的影響(下面的例子不能做為效能調整的根據，只是作為該引數的一個研究型文件，實際在調整該引數的時候，還是參考應用的型別（oltp/olap），及v$system_event 中的db file sequential read，db file scattered read的具體數值。

建立測試表

SQL> create table t as select * from dba_objects;

SQL> create index t_idx on t(owner);

 

SQL> SHOW PARAMETER OPTIMIZER_INDEX_COST_ADJ

NAME                                 TYPE        VALUE

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

optimizer_index_cost_adj             integer     100

SQL> SHOW PARAMETER DB_FILE

NAME                                 TYPE        VALUE

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

db_file_multiblock_read_count        integer     16

SQL> set autotrace traceonly explain;

SQL> select * from t where wner='HR';

Execution Plan

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

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=19 Card=1060 Bytes=91160)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=19 Card=1060 Bytes=91160)

   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=3 Card=1060)

 

SQL>  select /*+ FULL(T) */ * FROM T WHERE WNER='HR';

Execution Plan

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

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=41 Card=1060 Bytes=91160)

   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=41 Card=1060 Bytes=91160)

預設的optimizer_index_cost_adj =100，全表掃描及索引範圍掃描的成本19，41，此時索引掃描的成本要比全表掃描的成本低，調整optimizer_index_cost_adj 引數，調整的基點： 全表掃描成本/索引掃描成本

SQL> SELECT 41/19*100 FROM DUAL;

     41/19

----------

215.789474

SQL>  SET AUTOTRACE OFF;

先取一個稍微大於此基點的整數

SQL> ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=216;

會話已更改。

SQL>  set autotrace traceonly explain;

SQL> SET AUTOTRACE OFF;

SQL> SHOW PARAMETER OPTIMIZER_INDEX_COST_ADJ

NAME                                 TYPE        VALUE

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

optimizer_index_cost_adj             integer     216

SQL> set autotrace traceonly explain;

SQL> select * from t where wner='HR';

 

Execution Plan

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

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=41 Card=1060 Bytes=91160)

   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=41 Card=1060 Bytes=91160)

此時走了全表掃描，成本為41，再看索引掃描的成本是多少

SQL> SELECT /*+ INDEX(T T_IDX) */ * FROM t where wner='HR';

 

Execution Plan

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

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=42 Card=1060 Bytes=91160)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=42 Card=1060 Bytes=91160)

   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=3 Card=1060)

 

索引的成本變為42了，索引優化器選擇了全表掃描(42=ceil(19*2.16))

再來看看把該引數的值降低一點點，是否又走索引了呢?

SQL> ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=214;

會話已更改。

SQL> SELECT 2.14*19 FROM DUAL;

   2.14*19

----------

     40.66

SQL>  select * from t where wner='HR';

Execution Plan

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

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=41 Card=1060 Bytes=91160)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=41 Card=1060 Bytes=91160)

   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=3 Card=1060)

此時的確走了索引掃描，成本為41(2.14*19=40.66)，前面看到全表掃描的成本也是41，如果再調小一點，索引鎖秒的成本就會變為40，結果會更明顯一點

SQL> ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=210;

會話已更改。

SQL>  select * from t where wner='HR';

Execution Plan

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

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=40 Card=1060 Bytes=91160)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=40 Card=1060 Bytes=91160)

   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=3 Card=1060)

 

SQL>  select /*+ FULL(T) */ * FROM T WHERE WNER='HR';

Execution Plan

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

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=41 Card=1060 Bytes=91160)

   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=41 Card=1060 Bytes=91160)

 

再看降低索引掃描相對於全表掃描的成本的比例因子，來降低索引掃描的成本，情況類似，簡單列出

SQL>  select * from t where owner>='HR';

Execution Plan

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

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=41 Card=23670 Bytes=2035620)

   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=41 Card=23670 Bytes=2035620)

SQL>  select /*+ INDEX(T T_IDX) */ * FROM t where owner>='HR';

Execution Plan

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

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=397 Card=23670 Bytes=2035620)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=397 Card=23670Bytes=2035620)

   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=57 Card =23670)

設定optimizer_index_cost_adj=trunc(41/397 *100)=10

SQL> ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=10;

會話已更改。

SQL>  select * from t where owner>='HR';

Execution Plan

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

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=40 Card=23670 Bytes=2035620)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=40 Card=23670 Bytes=2035620)

   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=57 Card=23670)

 （注：本文是在參考了eygle大師的資料情況下做的實驗）

 

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

相關文章