Tuesday, July 8, 2014

About OPTIMIZER_INDEX_CACHING & OPTIMIZER_INDEX_COST_ADJ

Oracle's Optimizer_index_caching and Optimizer_index_cost_adj parameters are the most 
important parameters among all the init.ora (SPFILE) parameters. The Cost based optimizer 
extensively uses these 2 parameters values during the cost calculation for arriving the optimum 
execution plan.

OPTIMIZER_INDEX_CACHINGThis parameter controls the costing of an index probe in conjunction with a nested loop.  The range of 
values 0 to 100 for OPTIMIZER_INDEX_CACHING indicates percentage of index blocks in the buffer 
cache, which modifies the optimizer's assumptions about index caching for nested loops and IN-list 
iterators.  A value of 100 infers that 100% of the index blocks are likely to be found in the buffer cache 
and the optimizer adjusts the cost of an index probe or nested loop accordingly. Use caution when using 
this parameter because execution plans can change in favor of index caching.


OPTIMIZER_INDEX_COST_ADJ
This parameter can be used to adjust the cost of index probes.  The range of values is 1 to 10000.  The 
default value is 100, which means that indexes are evaluated as an access path based on the normal 
costing 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.


It is very easy to understand the impact of the first parameter, OPTIMIZER_INDEX_CACHING. It 
tells us that percentage of index blocks in the buffer cache affect the cost calculation.
However, the 2nd parameter, OPTIMIZER_INDEX_COST_ADJ confuses many DBAs and developers 
because the range of values accepted is 1 through 10000. Many experts interpret the meaning in 
a different way.  Any change in OPTIMIZER_INDEX_COST_ADJ results in table access cost being 
scaled down (up) to the current value divided by 100.

In other words, I would say, if the value is less than 100, then the Optimizer may choose  indexed 
access path OR if the value is equal to (greater than 100), then Optimizer may choose FULL TABLE 
SCAN access path.

You can also interpret the meaning in this way: less value of OPTIMIZER_INDEX_COST_ADJ 
indicates single block read time is cheaper;   more value of OPTIMIZER_INDEX_COST_ADJ indicates 
multi block read time is cheaper.

Optimizer_Index_Cost_Adj values …………………………………………………………………..>
1….10..20..30……..………………………..100…200…..500……1000…4000….8000……10000
<Consider Index Access …>                 <Consider FULL TABLE SCAN ………………………>         

Among the 2 parameters, Optimizer_Index_Cost_Adj is more aggressive than 
OPIMIZER_INDEX_CACHING.
Note that all tests were conducted on 9iR2 where the system statistics was not collected.
First let us set up 2 tables to test the theory.

SQL> desc sales
Name                Null?                    Type
----------------- --------                 ------------------
CUST_ID          NOT NULL         NUMBER(12)
STATE              NOT NULL         CHAR(2)
SALE_YEAR                               NUMBER(4)
SALE_MONTH                            NUMBER(2)
PROD_ID                                   NUMBER
QTY                                          NUMBER
AMT                                          NUMBER
COMM_1                                   VARCHAR2(50)
COMM_2                                   VARCHAR2(200)


SQL> select /*+ full(a) parallel(a,16) */ count(*) from sales a ;

COUNT(*)
----------
14,336,064 
-----------√† Big table to test different values.
SQL> desc state
Name                       Null?            Type
--------------------- --------         ------------------
STATEID                NOT NULL    CHAR(2)
NAME                                        VARCHAR2(30)
SOMETXT                                  VARCHAR2(3900)
9 rows there are in the table.
Case 1
Let us first see what will be the cost for a query with default values of 0 and 100 for 
optimizer_index_caching and optimizer_index_cost_adj respectively.

SQL> alter session set optimizer_index_caching = 0;

Session altered.

SQL> alter session set optimizer_index_cost_adj = 100 ;

Session altered.

SQL> explain plan for
2  select     a.cust_id , b.name
3  from   sales a, state b
4  where  a.state = b.stateid and
5         b.stateid  = 'GA'  ;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
| Id  | Operation                                         |  Name         | Rows   | Bytes   | Cost  |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                    |   573K |    15M  | 45551 |
|   1 |  NESTED LOOPS                                |                    |   573K |    15M  | 45551 |
|   2 |   TABLE ACCESS BY INDEX ROWID      | STATE         |     1    |    21     |     1    |
|*  3 |    INDEX UNIQUE SCAN                     | STATE_PK    |     1    |             |           |
|*  4 |   TABLE ACCESS FULL                       | SALES          |   573K |  4480K | 45550 |
------------------------------------------------------------------------------------------------------

So, the total cost of the query is 45,551 IO. Note also that when CPU costing is off, then the COST 
is nothing but total number of IO.

This is our base line data that will be compared with the remaining tests.
Case 2
I changed the OPTIMIZER_INDEX_COST_ADJ to 10 but kept the “default” value for 
OPTIMIZER_INDEX_CACHING.

SQL> alter session set optimizer_index_caching = 0 ;

Session altered.

SQL> alter session set optimizer_index_cost_adj = 10 ;

Session altered.

SQL> explain plan for
2  select     a.cust_id , b.name
3  from   sales a, state b
4  where  a.state = b.stateid and
5         b.stateid  = 'GA'  ;

Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                                         |  Name              | Rows  | Bytes    | Cost (%CPU)|
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                         |   573K|    15M   | 37683   (1) |
|   1 |  NESTED LOOPS                                |                         |   573K|    15M   | 37683   (1) |
|   2 |   TABLE ACCESS BY INDEX ROWID    | STATE               |     1    |    21     |     2  (50)    |
|*  3 |    INDEX UNIQUE SCAN                    | STATE_PK         |     1    |             |                   |
|   4 |   TABLE ACCESS BY INDEX ROWID    | SALES               |   573K | 4480K | 37682   (1) |
|*  5 |    INDEX RANGE SCAN                      | SALES_IDX_2    |   573K |           | 30095   (0) |
----------------------------------------------------------------------------------------------------------------

The Query plan is completely changed – from full table scan to indexed access on the SALES table.  
The total cost of the query came down to 37,683 from 45,551, the “TABLE ACCESS BY INDEX 
ROWID” cost is 37,682 that includes the cost of “ INDEX RANGE SCAN “ for SALES_IDX_2 30,095.
Case 3
In real life situation some amount of index leaf blocks are always cached in the memory.
Now I tell Oracle that 50 % of index blocks are cached in the SGA by changing the 
OPTIMIZER_INDEX_CACHING parameter.

Let us study the cost of the query.

SQL> alter session set optimizer_index_caching = 50 ;

Session altered.

SQL> alter session set optimizer_index_cost_adj = 10 ;

Session altered.

SQL> explain plan for
2  select     a.cust_id , b.name
3  from   sales a, state b
4  where  a.state = b.stateid and
5         b.stateid  = 'GA'  ;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
| Id  | Operation                                       |  Name            | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------------------------
|   0  | SELECT STATEMENT                       |                      |   573K|    15M| 36207   (0)  |
|   1  |  NESTED LOOPS                             |                      |   573K|    15M| 36207   (0)  |
|   2  |   TABLE ACCESS BY INDEX ROWID | STATE           |     1   |    21   |     2  (50)    |
|*  3 |    INDEX UNIQUE SCAN                  | STATE_PK      |     1   |           |                   |
|   4  |   TABLE ACCESS BY INDEX ROWID | SALES           |   573K|  4480K| 36206   (0) |
|*  5 |    INDEX RANGE SCAN                    | SALES_IDX_2 |   573K|          | 15344   (0) |
--------------------------------------------------------------------------------------------------------------


In Case -2 , the “INDEX RANGE SCAN” for SALES_IDX_2 was 30095, after changing the parameter 
OPTIMIZER_INDEX_CACHING to 50, it came to 15344, just half of what it was before.
The “TABLE ACCESS BY INDEX ROWID” for SALES table came down to 36206 from 37682.
Finally the total cost came down to 36,207 from 37,683.
Case 4
If you further increase the parameter OPTIMIZER_INDEX_CACHING, then the cost of INDEX SCAN 
will further come down.
Let us test this concept with a new value, 90.  

SQL> alter session set optimizer_index_caching = 90 ;

Session altered.

SQL> alter session set optimizer_index_cost_adj = 10 ;

Session altered.

SQL> explain plan for
2  select     a.cust_id , b.name
3  from   sales a, state b
4  where  a.state = b.stateid and
5         b.stateid  = 'GA'  ;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
| Id  | Operation                                   |  Name             | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                     |                       |   573K|    15M | 34980   (0)|
|    1 |  NESTED LOOPS                           |                       |   573K|    15M | 34980   (0)|
|    2 |   TABLE ACCESS BY INDEX ROWID | STATE            |     1   |    21    |     2  (50)  |
|*  3 |    INDEX UNIQUE SCAN                | STATE_PK        |     1   |           |                  |
|    4 |   TABLE ACCESS BY INDEX ROWID | SALES            |   573K|  4480K| 34979   (0)|
|*  5 |    INDEX RANGE SCAN                  | SALES_IDX_2  |   573K|           |  3069   (0) |
--------------------------------------------------------------------------------------------------------

The cost of INDEX RANGE SCAN has been reduced to 3069.


In conclusion


One thing you should remember, the above tests and various costs obtained did not tell you which 
query will run faster.
I just demonstrated the impact of changing those 2 parameters.

There is no rule of thumb to fix those 2 values. How ever, One thing is sure that the default value 
0 and 100 for OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ respectively are set 
for Data Warehouse System.

If your system is OLTP, then these 2 parameters values should be changed.  I would test the 
system with different values before choosing the correct values.

Setting 90 to OPTIMIZER_INDEX_CACHING and 10 (or 15) to OPTIMIZER_INDEX_COST_ADJ will 
perform good for OLTP system.

If you collect system statistics, then these 2 parameters play less importance role in deciding the 
execution plan.

No comments:

Post a Comment