OIC(A) again – 2

Continuing from the previous post, here is one more case when adjusting optimizer_index_cost_adj may hurt you.

Setup:

drop table t1 cascade constraints purge;
create table t1 (id, x, pad, constraint t1_pk primary key(id, x))
as
select trunc(rownum/10)
     , mod(rownum, 10)
     , s1.text
  from all_source s1, all_source s2
 where rownum <= 1e6;

exec dbms_stats.gather_table_stats(user, 't1', method_opt=>'for all columns size 1', cascade=>true, no_invalidate=>false)

alter session set optimizer_index_cost_adj = 100;
alter session set optimizer_index_caching  = 0;

explain plan for select * from t1 where x = :1;
@xp

Here’s the plan:

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|  7324K|  3076   (1)| 00:00:37 |
|*  1 |  TABLE ACCESS FULL| T1   |   100K|  7324K|  3076   (1)| 00:00:37 |
--------------------------------------------------------------------------

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

   1 - filter("X"=TO_NUMBER(:1))

Let’s now do same thing as previously (i.e. capture query cost depending on different OICA/OIC settings using OPT_PARAM hint) but using a very unusual execution path – index skip scan of T1_PK:

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_ss(t1 t1_pk) ' ||
                            ' 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

prompt Query cost in case of heap-organized table depending on OICA/OIC
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;

This sounds like a very unusual plan. After all, I have 100K distinct values out of total of 1M rows, meaning skip scan would need to run 100K range scans – and this is just too much. Here is a table of query cost depending on the OICA/OIC:

   OICA   OIC_0  OIC_10  OIC_20  OIC_30  OIC_40  OIC_50  OIC_60  OIC_70  OIC_80  OIC_90 OIC_100
------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- -------
    100    3598    3598    3598    3598    3598    3598    3598    3598    3598    3598    3598
     90    3238    3238    3238    3238    3238    3238    3238    3238    3238    3238    3238
     80    2878    2878    2878    2878    2878    2878    2878    2878    2878    2878    2878
     70    2519    2519    2519    2519    2519    2519    2519    2519    2519    2519    2519
     60    2159    2159    2159    2159    2159    2159    2159    2159    2159    2159    2159
     50    1799    1799    1799    1799    1799    1799    1799    1799    1799    1799    1799
     40    1439    1439    1439    1439    1439    1439    1439    1439    1439    1439    1439
     30    1079    1079    1079    1079    1079    1079    1079    1079    1079    1079    1079
     20     720     720     720     720     720     720     720     720     720     720     720
     10     360     360     360     360     360     360     360     360     360     360     360
      0       1       1       1       1       1       1       1       1       1       1       1

11 rows selected.

As long as OPTIMIZER_INDEX_COST_ADJ is changing, index skip scan cost is also changing – unexpectedly.
This feature was introduced in 10.2.0.5; I was unable to turn it off with _fix_control though:

SQL> @bug "skip scan"
Opt
     BUGNO VALUE SQL_FEATURE                              DESCRIPTION                                                      features
---------- ----- ---------------------------------------- ---------------------------------------------------------------- ----------
   6070954     1 QKSFM_ACCESS_PATH_6070954                No skip scan with contiguous leading equality index keys         10.2.0.4
   5714944     1 QKSFM_ACCESS_PATH_5714944                set IO cost for index skip scan to at least 1.0                  10.2.0.5
   7272039     1 QKSFM_ACCESS_PATH_7272039                use index cost adj when comparing skip scan with full table scan 10.2.0.5
   6086930     1 QKSFM_ACCESS_PATH_6086930                correct skip scan selectivity evaluation for BETWEEN predicate   11.2.0.2
   7277732     1 QKSFM_CBO_7277732                        allow skip scan costing for NL with non-join predicate           11.2.0.2
   8855396     1 QKSFM_ACCESS_PATH_8855396                sanity check for skip scan costing                               11.2.0.2
   8893626     1 QKSFM_ACCESS_PATH_8893626                apply index filter selectivity during skip scan costing          11.2.0.2
   9195582     1 QKSFM_ACCESS_PATH_9195582                leaf blocks as upper limit for skip scan blocks                  11.2.0.2
   4904838     1 QKSFM_CBO_4904838                        allow index skip scan with no index keys                         9.2.0.8

9 rows selected.

12.1.0.1 reports similar numbers:

   OICA   OIC_0  OIC_10  OIC_20  OIC_30  OIC_40  OIC_50  OIC_60  OIC_70  OIC_80  OIC_90 OIC_100
------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- -------
    100    3205    3205    3205    3205    3205    3205    3205    3205    3205    3205    3205
     90    2885    2885    2885    2885    2885    2885    2885    2885    2885    2885    2885
     80    2564    2564    2564    2564    2564    2564    2564    2564    2564    2564    2564
     70    2244    2244    2244    2244    2244    2244    2244    2244    2244    2244    2244
     60    1923    1923    1923    1923    1923    1923    1923    1923    1923    1923    1923
     50    1603    1603    1603    1603    1603    1603    1603    1603    1603    1603    1603
     40    1282    1282    1282    1282    1282    1282    1282    1282    1282    1282    1282
     30     962     962     962     962     962     962     962     962     962     962     962
     20     641     641     641     641     641     641     641     641     641     641     641
     10     321     321     321     321     321     321     321     321     321     321     321
      0       1       1       1       1       1       1       1       1       1       1       1