ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 参数Optimizer_index_cost_adj 对执行计划的影响

参数Optimizer_index_cost_adj 对执行计划的影响

原创 Linux操作系统 作者:oracle_kai 时间:2008-03-14 13:25:24 0 删除 编辑

参数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 readdb 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全表扫描及索引范围扫描的成本1941,此时索引扫描的成本要比全表扫描的成本低,调整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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-20

  • 博文量
    48
  • 访问量
    172801