OPTIMIZER_INDEX_COST_ADJ 参数

参考文档：

https://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams170.htm#REFRN10143
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/influencing-the-optimizer.html#GUID-C1C85DEA-3583-40FE-B5BB-6AC8F76FFE34

-- 创建测试用的表
create table t as select * from dba_objects;
select * from t where object_id >60000;
create index idx_t on t (object_id);

1 测试，将该参数调整为较小的值，比如调整为30

-- 会话1中
set autotrace traceonly
select * from t where object_id >60000;

--会话2 中
alter session set optimizer_index_cost_adj =30;
set autotrace traceonly
select * from t where object_id >60000;

 

-- 会话1的执行计划

SYS@test>select * from t where object_id >60000;

46166 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 35871 |    16M|   368   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 35871 |    16M|   368   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID">60000)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
       4452  consistent gets
          0  physical reads
          0  redo size
    2880013  bytes sent via SQL*Net to client
      34454  bytes received via SQL*Net from client
       3079  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      46166  rows processed

SYS@test>

--会话2 的执行计划

SYS@test>select * from t where object_id >60000;

46166 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 120220905

---------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       | 35871 |    16M|   334   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T     | 35871 |    16M|   334   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_T | 35871 |       |    28   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID">60000)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       7488  consistent gets
          0  physical reads
          0  redo size
    6344599  bytes sent via SQL*Net to client
      34454  bytes received via SQL*Net from client
       3079  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      46166  rows processed

SYS@test>

2  测试，将该参数调整为较大的值，比如调整为120
-- 会话1中
set autotrace traceonly
select * from t where object_id >60000;

--会话2 中
alter session set optimizer_index_cost_adj =120;
set autotrace traceonly
select * from t where object_id >60000;

-- 会话2中的执行计划

SYS@test>alter session set optimizer_index_cost_adj =120;

Session altered.

SYS@test>select * from t where object_id >60000;

46166 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 35871 |    16M|   368   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 35871 |    16M|   368   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID">60000)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
       4452  consistent gets
          0  physical reads
          0  redo size
    2880013  bytes sent via SQL*Net to client
      34454  bytes received via SQL*Net from client
       3079  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      46166  rows processed

SYS@test>

结论
这个参数，表示索引扫描和全表扫描的成本的一个比较。缺省值100，表示索引扫描成本，等价转换全表扫描成本。
1 改参数的默认值是100. 当该参数设置为较小的值得时候，比如设置为30 ，更加倾向于使用索引。
2 当该参数设置为较大的值得时候，比如设置为120，更加倾向于使用全表扫描 。

 

官方文档的一些解释：

OPTIMIZER_INDEX_COST_ADJ

Property	Description
Parameter type	Integer
Default value	100
Modifiable	ALTER SESSION, ALTER SYSTEM
Range of values	1 to 10000

 

OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.

The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.

 

OPTIMIZER_INDEX_COST_ADJ

Adjusts the cost of index probes. The range of values is 1 to 10000. The default value is 100, which means that the optimizer evaluates indexes as an access path based on the normal cost model. A value of 10 means that the cost of an index access path is one-tenth the normal cost of an index access path.

 

 

END

 

-- 2020-09-10 补充，从其他书上看到的

END