OPTIMIZER_INDEX_COST_ADJ

Parameter "optimizer_index_cost_adj" was firstly introduced way back in Oracle 8.0.5.
This is very tricky parameter that gets used for calculating cost and we could use this parameter for tuning optimizer behavior. Default value of this parameter is 100, at which the optimizer evaluates index access paths at regular cost. Range of values for this parameter is from 1 to 10000. Changing this value you can influence on optimizer to use index access path over full table scan or opposite. Using lower number then 100 you make index access more desirable for CBO because lower values of that parameter are lowering the cost of index access path.

So changing value of this parameter can improve performance of some queries but also it can significantly degrade the performance if index cost is reduced to unrealistic level and indexes are chosen over other more appropriate methods. I will show this in my example.




First I will create testing table with primary key and index.

msutic@TEST11> CREATE TABLE test_tab AS
2  SELECT  level id                                ,
3          ROUND(dbms_random.value(1,9999),0)   Num,
4          sysdate+level Day                       ,
5          dbms_random.string('A',10) Name
6  FROM    dual
7  CONNECT BY level <= 1000000;

Table created.

msutic@TEST11> ALTER TABLE test_tab
2  ADD CONSTRAINT pk_test_tab PRIMARY KEY (id);

Table altered.

msutic@TEST11> CREATE INDEX idx_num ON test_tab(num);

Index created.

msutic@TEST11> begin
2  dbms_stats.gather_table_stats( ownname=>'MSUTIC',
3                                     tabname=>'TEST_TAB',
4                                     cascade => true,
5                                     method_opt => 'for all indexed columns size 251',
6                                     estimate_percent=>dbms_stats.auto_sample_size
7                                   );
8  end;
9  /

PL/SQL procedure successfully completed.

With optimizer_index_cost_adj = 100 I get this result:

msutic@TEST11> select * from test_tab a, test_tab b
2  where a.id=b.id+5000
3  and b.num > 9950;

4867 rows selected.

Elapsed: 00:00:03.18

Execution Plan
----------------------------------------------------------
Plan hash value: 4193374725

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |  5401 |   284K|  2560   (2)| 00:00:31 |
|*  1 |  HASH JOIN         |          |  5401 |   284K|  2560   (2)| 00:00:31 |
|*  2 |   TABLE ACCESS FULL| TEST_TAB |  5401 |   142K|  1277   (2)| 00:00:16 |
|   3 |   TABLE ACCESS FULL| TEST_TAB |  1000K|    25M|  1276   (2)| 00:00:16 |
-------------------------------------------------------------------------------

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

1 - access("A"."ID"="B"."ID"+5000)
2 - filter("B"."NUM">9950)


Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
9148  consistent gets
9130  physical reads
0  redo size
280578  bytes sent via SQL*Net to client
476  bytes received via SQL*Net from client
11  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
4867  rows processed

The most appropriate path for executing this query is using full scans of the table. Execution lasted 00:00:03.18 secs and with 9148 consistent gets and 9130 physical reads.

Now I will change parameter to 10 (optimizer_index_cost_adj=10) to make indexes more desirable.

msutic@TEST11> alter session set optimizer_index_cost_adj=10;

Session altered.

msutic@TEST11> select * from test_tab a, test_tab b
2  where a.id=b.id+5000
3  and b.num > 9950;

4867 rows selected.

Elapsed: 00:00:18.26

Execution Plan
----------------------------------------------------------
Plan hash value: 2874762042

-----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |  5401 |   284K|       |  1249   (1)| 00:00:15 |
|   1 |  MERGE JOIN                   |             |  5401 |   284K|       |  1249   (1)| 00:00:15 |
|   2 |   TABLE ACCESS BY INDEX ROWID | TEST_TAB    |  1000K|    25M|       |   669   (1)| 00:00:09 |
|   3 |    INDEX FULL SCAN            | PK_TEST_TAB |  1000K|       |       |   210   (1)| 00:00:03 |
|*  4 |   SORT JOIN                   |             |  5401 |   142K|   440K|   580   (1)| 00:00:07 |
|   5 |    TABLE ACCESS BY INDEX ROWID| TEST_TAB    |  5401 |   142K|       |   536   (0)| 00:00:07 |
|*  6 |     INDEX RANGE SCAN          | IDX_NUM     |  5401 |       |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

4 - access("A"."ID"="B"."ID"+5000)
filter("A"."ID"="B"."ID"+5000)
6 - access("B"."NUM">9950)


Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
11533  consistent gets
8666  physical reads
0  redo size
280578  bytes sent via SQL*Net to client
476  bytes received via SQL*Net from client
11  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
4867  rows processed

Significant performance degradation is more then obvious. Query execution lasted 00:00:18.26 secs with 11533 consistent gets and 8666 physical reads.
You could see that CBO has chosen indexes over full table scans to execute query.

I've executed this example on:

Linux ciciban 2.6.18-92.1.10.el5.centos.plus #1 SMP Thu Aug 7 12:19:36 EDT 2008 i686 i686 i386 GNU/Linux

Oracle version 11.1.0.6.0

I will execute same query on different operating system and different Oracle version just to compare results.
I've already made test table with all settings like on machine before.

msutic@XE> select * from test_tab a, test_tab b
2  where a.id=b.id+5000
3  and b.num > 9950;

4821 rows selected.

Elapsed: 00:00:00.29

Execution Plan
----------------------------------------------------------
Plan hash value: 4193374725

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |  4963 |   261K|  2620   (4)| 00:00:32 |
|*  1 |  HASH JOIN         |          |  4963 |   261K|  2620   (4)| 00:00:32 |
|*  2 |   TABLE ACCESS FULL| TEST_TAB |  4963 |   130K|  1302   (4)| 00:00:16 |
|   3 |   TABLE ACCESS FULL| TEST_TAB |   996K|    25M|  1300   (4)| 00:00:16 |
-------------------------------------------------------------------------------

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

1 - access("A"."ID"="B"."ID"+5000)
2 - filter("B"."NUM">9950)


Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
9156  consistent gets
0  physical reads
0  redo size
278485  bytes sent via SQL*Net to client
483  bytes received via SQL*Net from client
11  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
4821  rows processed

With optimizer_index_cost_adj=10:

msutic@XE> alter session set optimizer_index_cost_adj=10;

Session altered.

Elapsed: 00:00:00.12
msutic@XE> select * from test_tab a, test_tab b
2  where a.id=b.id+5000
3  and b.num > 9950;

4821 rows selected.

Elapsed: 00:00:01.31

Execution Plan
----------------------------------------------------------
Plan hash value: 2874762042

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |  4963 |   261K|  1169   (1)| 00:00:15 |
|   1 |  MERGE JOIN                   |             |  4963 |   261K|  1169   (1)| 00:00:15 |
|   2 |   TABLE ACCESS BY INDEX ROWID | TEST_TAB    |   996K|    25M|   673   (2)| 00:00:09 |
|   3 |    INDEX FULL SCAN            | PK_TEST_TAB |   996K|       |   212   (2)| 00:00:03 |
|*  4 |   SORT JOIN                   |             |  4963 |   130K|   496   (1)| 00:00:06 |
|   5 |    TABLE ACCESS BY INDEX ROWID| TEST_TAB    |  4963 |   130K|   495   (1)| 00:00:06 |
|*  6 |     INDEX RANGE SCAN          | IDX_NUM     |  4980 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

4 - access("A"."ID"="B"."ID"+5000)
filter("A"."ID"="B"."ID"+5000)
6 - access("B"."NUM">9950)


Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
11483  consistent gets
1  physical reads
0  redo size
278485  bytes sent via SQL*Net to client
483  bytes received via SQL*Net from client
11  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
4821  rows processed

This is executed on Windows XP operating system and Oracle 10.2.0.1.0 version.
Performance degradation on this machine is less obvious then before but still it exists.


Parameter optimizer_index_cost_adj is tightly related with optimizer_index_caching parameter but I will talk more about that parameter in one of next posts.
The big question is what is most appropriate nondefault value for this parameters? One thing is sure that changing values of this parameters you have immediate impact on CBO so it is wisely to test effects of this parameters before activating them on production.

Two extremes:
Quote from "Effective Oracle by Design" book of Tom Kyte:

You should consider setting these to nondefault values for many systems, or at least testing the

two extremes:

o The default settings of OPTIMIZER_INDEX_CACHING = 0 and OPTIMIZER_INDEX_COST_ADJ = 100. These are
typically appropriate for many data warehouse/reporting systems.

o The settings of OPTIMIZER_INDEX_CACHING = 90 and OPTIMIZER_INDEX_COST_ADJ = 25. These are
typically appropriate for many transactional/OLTP systems.

REFERENCE:
Documentation: Optimizer_index_cost_adj