参数Optimizer_index_cost_adj 对执行计划的影响

参数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=19Card=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=3Card=1060)

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

Execution Plan

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

  0     SELECT STATEMENT ptimizer=CHOOSE (Cost=41Card=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=41Card=1060 Bytes=91160)

此时走了全表扫描，成本为41，再看索引扫描的成本是多少

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

Execution Plan

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

  0     SELECT STATEMENT ptimizer=CHOOSE (Cost=42Card=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=3Card=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=41Card=1060 Bytes=91160)

  1   0  TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=41Card=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=40Card=1060 Bytes=91160)

  1   0  TABLE ACCESS (BY INDEX ROWID) OF 'T'(Cost=40Card=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=41Card=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=41Card=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=397Card=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=40Card=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大师的资料情况下做的实验）