OIC(A) again

Issues with OICA/OIC (OPTIMIZER_INDEX_COST_ADJ/OPTIMIZER_INDEX_CACHING) parameters have already been mentioned many times. Recently I’ve noticed one more and I think I didn’t see this case somewhere else on the Internet so I’ll share it.

Here’s a simple table T1 holding 1M rows with the indexed column X having 10 distinct values:

drop table t1 cascade constraints purge;
create table t1 (id, x, pad, constraint t1_pk primary key(id))
as
select rownum
     , mod(rownum, 10)
     , lpad('x', 200, 'x')
  from all_source s1, all_source s2
 where rownum <= 1e6;
 
create index t1_indx on t1(x);
exec dbms_stats.gather_table_stats(user, 't1', method_opt=>'for all columns size 1', cascade=>true, no_invalidate=>false)

I run multiple EXPLAIN PLANs for a query accessing T1 by a single X value using T1_INDX range scan. I know it’s a very inefficient way to access the data in this case, but it fits perfectly well for my purposes. I’m using different OICA/OIC settings with the help of OPT_PARAM hint and print the resulting IO cost for the query based on the PLAN_TABLE data:

delete plan_table;
commit;

set serveroutput on
declare
  l_query varchar2(4000);
begin
  for oica in 0..10 loop
    for oic in 0..10 loop
      l_query := 'explain plan set statement_id = ''' || oica*10 || ',' || oic*10 || ''' for ' ||
                  'select /*+ index_rs(t1 t1_indx) ' || 
                            ' opt_param(''optimizer_index_cost_adj'' ' || oica*10 || ') ' ||
                            ' opt_param(''optimizer_index_caching'' ' || oic*10 || ') */ * ' ||
                  '  from t1 ' ||
                  ' where x = :1';
--      dbms_output.put_line(l_query);
      execute immediate l_query;
    end loop;
  end loop;
end;
/

set numwidth 7

select *
  from (select to_number(substr(statement_id, 1, instr(statement_id, ',') - 1)) oica
              ,to_number(substr(statement_id, instr(statement_id, ',') + 1)) oic
              ,io_cost
          from plan_table
         where id = 0) 
  pivot(max(io_cost) for oic in (0  as oic_0
                                ,10 as oic_10
                                ,20 as oic_20
                                ,30 as oic_30
                                ,40 as oic_40
                                ,50 as oic_50
                                ,60 as oic_60
                                ,70 as oic_70
                                ,80 as oic_80
                                ,90 as oic_90
                                ,100 as oic_100))
 order by oica desc;

The results are expected:

   OICA   OIC_0  OIC_10  OIC_20  OIC_30  OIC_40  OIC_50  OIC_60  OIC_70  OIC_80  OIC_90 OIC_100
------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- -------
    100   30499   30499   30499   30499   30499   30499   30499   30499   30499   30499   30499
     90   27449   27449   27449   27449   27449   27449   27449   27449   27449   27449   27449
     80   24399   24399   24399   24399   24399   24399   24399   24399   24399   24399   24399
     70   21349   21349   21349   21349   21349   21349   21349   21349   21349   21349   21349
     60   18299   18299   18299   18299   18299   18299   18299   18299   18299   18299   18299
     50   15250   15250   15250   15250   15250   15250   15250   15250   15250   15250   15250
     40   12200   12200   12200   12200   12200   12200   12200   12200   12200   12200   12200
     30    9150    9150    9150    9150    9150    9150    9150    9150    9150    9150    9150
     20    6100    6100    6100    6100    6100    6100    6100    6100    6100    6100    6100
     10    3050    3050    3050    3050    3050    3050    3050    3050    3050    3050    3050
      0       1       1       1       1       1       1       1       1       1       1       1

Since the table is around 30K blocks and the data associated with any given value X is spread across all of the table blocks and the data is poorly clustered, I’m expecting the query cost to be around the number of table blocks, with table access being the major contributor, and it’s true by default:

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |   100K|    19M| 30511   (1)| 00:06:07 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |   100K|    19M| 30511   (1)| 00:06:07 |
|*  2 |   INDEX RANGE SCAN          | T1_INDX |   100K|       |   197   (1)| 00:00:03 |
---------------------------------------------------------------------------------------

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

   2 - access("X"=TO_NUMBER(:1))

With the changes of OICA, the query cost changes almost linearly, except for some rounding error. Changes to the OIC do not impact query cost, as the documentation suggests:

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

And I have nor NLJ, nor IN-list iterator here.

Now let’s change the table layout: make it index-organized and run the same PL/SQL test & query for the PLAN_TABLE results

drop table t1 cascade constraints purge;
create table t1 (id, x, pad, constraint t1_pk primary key(id))
organization index
as
select rownum
     , mod(rownum, 10)
     , lpad('x', 200, 'x')
  from all_source s1, all_source s2
 where rownum <= 1e6;
 
create index t1_indx on t1(x);
exec dbms_stats.gather_table_stats(user, 't1', method_opt=>'for all columns size 1', cascade=>true, no_invalidate=>false)

   OICA   OIC_0  OIC_10  OIC_20  OIC_30  OIC_40  OIC_50  OIC_60  OIC_70  OIC_80  OIC_90 OIC_100
------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- -------
    100   30582     255     226     198     170     142     113      85      57      29   30304
     90   27524     230     203     178     153     128     102      77      51      26   27274
     80   24466     204     181     158     136     114      90      68      46      23   24243
     70   21407     179     158     139     119      99      79      60      40      20   21213
     60   18349     153     136     119     102      85      68      51      34      17   18182
     50   15291     128     113      99      85      71      57      43      29      15   15152
     40   12233     102      90      79      68      57      45      34      23      12   12122
     30    9175      77      68      59      51      43      34      26      17       9    9091
     20    6116      51      45      40      34      28      23      17      11       6    6061
     10    3058      26      23      20      17      14      11       9       6       3    3030
      0       1       1       1       1       1       1       1       1       1       1       1

This time the results are amusing. As long as the OIC doesn’t change, everything goes pretty much similar to the heap-organized table, with the query cost being a little bit higher – that’s OK as secondary indexes, based on logical rowids, may be slightly less efficient than indexes on heap tables.
When the OIC changes, everything gets too crazy. By modifying the CBO index caching assumption to a mere 10%, the query cost drops by two orders of magnitude! This is tremendous shift in the costing which poses terrific consequences for legacy applications trying to utilize IOTs advantages.
It looks like the issue was introduced by the bug fix 5236908, which is described in the V$SESSION_FIX_CONTROL as “Allow _optimizer_index_caching to affect IOT primary keys” and was introduced in the 10.2.0.4. Probably they did it wrong: they’ve discounted UQ scan completely, thus dropping major part of the cost in some cases:

SQL> explain plan for select /*+ index(t1 t1_indx) opt_param('optimizer_index_caching' 10) */ * from t1 where x = :1;

Explained.

SQL> @xp

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
Plan hash value: 728231638

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |   100K|    19M|   257   (1)| 00:00:04 |
|*  1 |  INDEX UNIQUE SCAN| T1_PK   |   100K|    19M|   257   (1)| 00:00:04 |
|*  2 |   INDEX RANGE SCAN| T1_INDX |   100K|       |   256   (1)| 00:00:04 |
-----------------------------------------------------------------------------

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

   1 - access("X"=TO_NUMBER(:1))
   2 - access("X"=TO_NUMBER(:1))

SQL> explain plan for select /*+ index(t1 t1_indx) opt_param('optimizer_index_caching' 10) */ * from t1 where id in (:1);

Explained.

SQL> @xp

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
Plan hash value: 426392120

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |   209 |     0   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| T1_PK |     1 |   209 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("ID"=TO_NUMBER(:1))

SQL> explain plan for select /*+ index(t1 t1_indx) opt_param('optimizer_index_caching' 10) */ * from t1 where id in (:1, :2);

Explained.

SQL> @xp

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1529266855

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     2 |   418 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR   |       |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| T1_PK |     2 |   418 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - access("ID"=TO_NUMBER(:1) OR "ID"=TO_NUMBER(:2))

The bug fix can’t be turned off with _fix_control for some reason and I don’t have an old Oracle database to run the test so I can’t confirm if the bug-fix is the culprit or not. Anyway it looks stupid. I’ll try to submit bug report for this issue.