Home > TSOP > OPTIMIZER_INDEX_CACHING&optimizer_index_cost_adj

OPTIMIZER_INDEX_CACHING&optimizer_index_cost_adj

翻书的时候看到的,挺有意思的2个参数,看下doc(11g)上的解释:

OPTIMIZER_INDEX_CACHING

Property
Description

Parameter type
Integer

Default value
0

Modifiable
ALTER SESSION, ALTER SYSTEM

Range of values
0 to 100

OPTIMIZER_INDEX_CACHING lets you adjust the behavior of cost-based optimization to favor nested loops joins and IN-list iterators.

The cost of executing an index using an IN-list iterator or of executing a nested loops join when an index is used to access the inner table depends on the caching of that index in the buffer cache. The amount of caching depends on factors that the optimizer cannot predict, such as the load on the system and the block access patterns of different users.

You can modify the optimizer’s assumptions about index caching for nested loops joins and IN-list iterators by setting this parameter to a value between 0 and 100 to indicate the percentage of the index blocks the optimizer should assume are in the cache. Setting this parameter to a higher value makes nested loops joins and IN-list iterators look less expensive to the optimizer. As a result, it will be more likely to pick nested loops joins over hash or sort-merge joins and to pick indexes using IN-list iterators over other indexes or full table scans. The default for this parameter is 0, which results in default optimizer behavior.

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_caching,按照官方解释,这个参数只会影响nl跟in-list plan的选择性,理论的东西没有真实体验到心里总觉得虚,不靠谱,试验之:

optimizer_index_caching

tmp_johnny/TMP_JOHNNY@PERFDB_2>create table t(id number,name char(100));
tmp_johnny/TMP_JOHNNY@PERFDB_2>create table m(id number,name char(100));
tmp_johnny/TMP_JOHNNY@PERFDB_2>set feedb on
tmp_johnny/TMP_JOHNNY@PERFDB_2>insert into t select level,'qif'||level from dual connect by level<=10000;

10000 rows created.

tmp_johnny/TMP_JOHNNY@PERFDB_2>insert into m select level,'qif'||level from dual connect by level<=10000;

10000 rows created.

tmp_johnny/TMP_JOHNNY@PERFDB_2>create index t_ind on t(id);

Index created.

tmp_johnny/TMP_JOHNNY@PERFDB_2>create index m_ind on m(id);

Index created.

tmp_johnny/TMP_JOHNNY@PERFDB_2>show parameter optimizer_index_caching

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_caching              integer     0  --按照官方的意思,optimizer认为index在cache中为0
tmp_johnny/TMP_JOHNNY@PERFDB_2>explain plan for select t.* from t,m where t.id<2 and t.id=m.id;

Explained.

tmp_johnny/TMP_JOHNNY@PERFDB_2>select * from table(dbms_xplan.display('','','basic'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3811644848

----------------------------------------------
| Id  | Operation                    | Name  |  --看来optimizer认为索引块物理读+一次loop效率还是高于其他Plan的,这个必须的
----------------------------------------------
|   0 | SELECT STATEMENT             |       |
|   1 |  NESTED LOOPS                |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| T     |
|   3 |    INDEX RANGE SCAN          | T_IND |
|   4 |   INDEX RANGE SCAN           | M_IND |
----------------------------------------------

11 rows selected.

tmp_johnny/TMP_JOHNNY@PERFDB_2>explain plan for select t.* from t,m where t.id<10 and t.id=m.id;

Explained.

tmp_johnny/TMP_JOHNNY@PERFDB_2>select * from table(dbms_xplan.display('','','basic'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4013230605

----------------------------------------------
| Id  | Operation                    | Name  |  --9次loop就不可接受了,改走hash_join
----------------------------------------------
|   0 | SELECT STATEMENT             |       |
|   1 |  HASH JOIN                   |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| T     |
|   3 |    INDEX RANGE SCAN          | T_IND |
|   4 |   INDEX RANGE SCAN           | M_IND |
----------------------------------------------

11 rows selected.

tmp_johnny/TMP_JOHNNY@PERFDB_2>alter session set optimizer_index_caching=100;

Session altered.

tmp_johnny/TMP_JOHNNY@PERFDB_2>explain plan for select t.* from t,m where t.id<10 and t.id=m.id;

Explained.

tmp_johnny/TMP_JOHNNY@PERFDB_2>select * from table(dbms_xplan.display('','','basic'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3811644848

----------------------------------------------
| Id  | Operation                    | Name  |  --这个正常
----------------------------------------------
|   0 | SELECT STATEMENT             |       |
|   1 |  NESTED LOOPS                |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| T     |
|   3 |    INDEX RANGE SCAN          | T_IND |
|   4 |   INDEX RANGE SCAN           | M_IND |
----------------------------------------------

11 rows selected.

tmp_johnny/TMP_JOHNNY@PERFDB_2>explain plan for select t.* from t,m where t.id<=10000 and t.id=m.id;

Explained.

tmp_johnny/TMP_JOHNNY@PERFDB_2>select * from table(dbms_xplan.display('','','basic'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4164045099

------------------------------------
| Id  | Operation          | Name  |  --这个plan就超级差了
------------------------------------
|   0 | SELECT STATEMENT   |       |
|   1 |  NESTED LOOPS      |       |
|   2 |   TABLE ACCESS FULL| T     |
|   3 |   INDEX RANGE SCAN | M_IND |
------------------------------------

10 rows selected.

optimizer_index_cost_adj

tmp_johnny/TMP_JOHNNY@PERFDB_2>show parameter optimizer_index_cost_adj

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj             integer     100  --默认是index的开销是FTS的100%
tmp_johnny/TMP_JOHNNY@PERFDB_2>alter session set optimizer_index_cost_adj=1; --1:100

Session altered.

tmp_johnny/TMP_JOHNNY@PERFDB_2>explain plan for select t.* from t where t.id<=10000;

Explained.

tmp_johnny/TMP_JOHNNY@PERFDB_2>select * from table(dbms_xplan.display('','','basic'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1376202287

---------------------------------------------
| Id  | Operation                   | Name  |  --所有记录也range scan了
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |
|   2 |   INDEX RANGE SCAN          | T_IND |
---------------------------------------------

9 rows selected.

tmp_johnny/TMP_JOHNNY@PERFDB_2>alter session set optimizer_index_cost_adj=10000;  --100:1

Session altered.

tmp_johnny/TMP_JOHNNY@PERFDB_2>explain plan for select t.* from t where t.id<=1;

Explained.

tmp_johnny/TMP_JOHNNY@PERFDB_2>select * from table(dbms_xplan.display('','','basic'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1601196873

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |  --1条记录也FTS
|   1 |  TABLE ACCESS FULL| T    |
----------------------------------

8 rows selected.

看来这2个参数对CBO的影响还是很大的,默认的设置显然不适合OLTP的场景,caching至少要大于0吧,目前线上大部分索引的non-leaf block 还是在cache中的,cost_adj应该尽量小点,让CBO更倾向于走INDEX。

  1. No comments yet.
  1. No trackbacks yet.