Optimizer uses wrong index

Question

We have a quite simple select statement accessing data with unique index fields. Nevertheless, the optimizer decides to use a bad index and the selection takes about 4 seconds instead of 0.0x seconds.

This problem doesn't exist with oracle 11g but with oracle 12c.

The table statistics are up to date.

It seems, the estimation for the bad plan is wrong (see below), how can we avoid this?

I guess by adding field statistics or baseline entries, but I hope there is another solution.

Thanks in advance.

Table Definition

create table PS_CS_AKT_PROD_TB(business_unit VARCHAR2(5) not null,
                               ra_cmpgn_wave_id VARCHAR2(15) not null,
                               product_id VARCHAR2(18) not null,
                               cs_aboart_cd VARCHAR2(20) not null,
                               cs_einweis_id VARCHAR2(20) not null,
                               row_added_dttm TIMESTAMP(6),
                               row_added_oprid VARCHAR2(30) not null,
                               row_lastmant_dttm TIMESTAMP(6),
                               row_lastmant_oprid VARCHAR2(30) not null,
                               cs_recstat_xl VARCHAR2(4) not null,
                               /* ... further fields ... */
                               cs_kondition VARCHAR2(20) not null) 
                               tablespace CS_APP pctfree 10 initrans 1 
                               maxtrans 255 storage(initial 40K next 104K
                               minextents 1 maxextents unlimited);

Indexes

create unique index PS_CS_AKT_PROD_TB on PS_CS_AKT_PROD_TB(
BUSINESS_UNIT,RA_CMPGN_WAVE_ID,PRODUCT_ID,CS_ABOART_CD) 
tablespace PSINDEX pctfree 10 initrans 2 maxtrans 255 
storage(initial 40K next 104K minextents 1 maxextents unlimited);

create index PSBCS_AKT_PROD_TB on PS_CS_AKT_PROD_TB(
BUSINESS_UNIT,PRODUCT_ID) 
tablespace PSINDEX pctfree 10 initrans 2 maxtrans 255 
storage(initial 40K next 104K minextents 1 maxextents unlimited);

Table Size

select count(*) from PS_CS_AKT_PROD_TB;
--> 6372395

Select Statement

All required fields of the unique index are given:

SELECT CS_STEUERUNG_XL, CS_EWF2EVT
  FROM PS_CS_AKT_PROD_TB
 WHERE BUSINESS_UNIT = :1
   AND RA_CMPGN_WAVE_ID = :2
   AND PRODUCT_ID = :3
   AND CS_ABOART_CD = :4;

Execution Details and Explain Plan

select v.CHILD_NUMBER,
       elapsed_time / 1000000 elapsed_time,
       executions,
       round((elapsed_time / decode(executions, 0, 1, executions)) /
             1000000,
             4) elapsed_time_per_exec,
       disk_reads,
       buffer_gets,
       rows_processed,
       cpu_time
  from v$sql v
 where v.SQL_ID = 'dqrktmcraprvp';

  /*
  CHILD_NUMBER  ELAPSED_TIME  EXECUTIONS  ELAPSED_TIME_PER_EXEC DISK_READS  BUFFER_GETS ROWS_PROCESSED  CPU_TIME
  0             400,874709    100         4,0087                98457       495295      86              5929096
  1             0,017217      8           0,0022                2           36          4               2108
  2             0,002038      2           0,001                 0           9           1               0
  */

 select plan_table_output
 from table(dbms_xplan.display_cursor('dqrktmcraprvp', 0)) t;

 /*
    SQL_ID  dqrktmcraprvp, child number 0
    -------------------------------------
    SELECT CS_STEUERUNG_XL, CS_EWF2EVT FROM PS_CS_AKT_PROD_TB WHERE 
    BUSINESS_UNIT = :1 AND RA_CMPGN_WAVE_ID = :2 AND PRODUCT_ID = :3 AND 
    CS_ABOART_CD = :4

    Plan hash value: 1118713352

    ---------------------------------------------------------------------------------------------------------
    | Id  | Operation                           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                    |                   |       |       |     1 (100)|          |
    |*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PS_CS_AKT_PROD_TB |     1 |    46 |     1   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN                  | PSBCS_AKT_PROD_TB |     1 |       |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------------------

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

       1 - filter(("RA_CMPGN_WAVE_ID"=:2 AND "CS_ABOART_CD"=:4))
       2 - access("BUSINESS_UNIT"=:1 AND "PRODUCT_ID"=:3)     */ 


 select plan_table_output
 from table(dbms_xplan.display_cursor('dqrktmcraprvp', 1)) t;

    /*

    SQL_ID  dqrktmcraprvp, child number 1
    -------------------------------------
    SELECT CS_STEUERUNG_XL, CS_EWF2EVT FROM PS_CS_AKT_PROD_TB WHERE 
    BUSINESS_UNIT = :1 AND RA_CMPGN_WAVE_ID = :2 AND PRODUCT_ID = :3 AND 
    CS_ABOART_CD = :4

    Plan hash value: 619225732

    -------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |                   |       |       |     1 (100)|          |
    |   1 |  TABLE ACCESS BY INDEX ROWID| PS_CS_AKT_PROD_TB |     1 |    46 |     1   (0)| 00:00:01 |
    |*  2 |   INDEX UNIQUE SCAN         | PS_CS_AKT_PROD_TB |     1 |       |     1   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------

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

       2 - access("BUSINESS_UNIT"=:1 AND "RA_CMPGN_WAVE_ID"=:2 AND "PRODUCT_ID"=:3 AND 
                  "CS_ABOART_CD"=:4)

    */    

Amendment (see comments)

Field values of PROCUCT_ID are unequally distributed

OCCURENCE_OF_PRODUCT_ID TOTAL
upto 10^1-1             1134
upto 10^2-1             1607
upto 10^3-1             1649
upto 10^4-1             455
upto 10^5-1             279

Output of dbms_xplan.display_cursor(null, null, '+OUTLINE')

upto 37 seconds:

    SQL_ID  ga79yhh54r5bu, child number 0
    -------------------------------------
    select a.cs_ewf2evt, a.cs_steuerung_xl from ps_cs_akt_prod_tb a where 
    a.business_unit = :1 and a.ra_cmpgn_wave_id = :2 and a.product_id = :3 
    and a.cs_aboart_cd = :4

    Plan hash value: 1118713352

    ---------------------------------------------------------------------------------------------------------
    | Id  | Operation                           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                    |                   |       |       |     1 (100)|          |
    |*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PS_CS_AKT_PROD_TB |     1 |    46 |     1   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN                  | PSBCS_AKT_PROD_TB |     1 |       |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------------------

    Outline Data
    -------------

      /*+
          BEGIN_OUTLINE_DATA
          IGNORE_OPTIM_EMBEDDED_HINTS
          OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
          DB_VERSION('12.1.0.2')
          OPT_PARAM('_optimizer_max_permutations' 50)
          OPT_PARAM('_unnest_subquery' 'false')
          OPT_PARAM('optimizer_dynamic_sampling' 4)
          OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
          OPT_PARAM('_optimizer_use_feedback' 'false')
          OPT_PARAM('_px_adaptive_dist_method' 'off')
          OPT_PARAM('_optimizer_dsdir_usage_control' 0)
          OPT_PARAM('_optimizer_adaptive_plans' 'false')
          OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
          OPT_PARAM('_optimizer_gather_feedback' 'false')
          OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
          OPT_PARAM('optimizer_index_cost_adj' 20)
          ALL_ROWS
          OUTLINE_LEAF(@"SEL$1")
          INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("PS_CS_AKT_PROD_TB"."BUSINESS_UNIT" 
                  "PS_CS_AKT_PROD_TB"."PRODUCT_ID"))
          BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "A"@"SEL$1")
          END_OUTLINE_DATA
      */

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

       1 - filter(("A"."RA_CMPGN_WAVE_ID"=:2 AND "A"."CS_ABOART_CD"=:4))
       2 - access("A"."BUSINESS_UNIT"=:1 AND "A"."PRODUCT_ID"=:3)

~ 0.06 seconds

    SQL_ID  ga79yhh54r5bu, child number 0
    -------------------------------------
    select a.cs_ewf2evt, a.cs_steuerung_xl from ps_cs_akt_prod_tb a where 
    a.business_unit = :1 and a.ra_cmpgn_wave_id = :2 and a.product_id = :3 
    and a.cs_aboart_cd = :4

    Plan hash value: 619225732

    -------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |                   |       |       |     1 (100)|          |
    |   1 |  TABLE ACCESS BY INDEX ROWID| PS_CS_AKT_PROD_TB |     1 |    46 |     1   (0)| 00:00:01 |
    |*  2 |   INDEX UNIQUE SCAN         | PS_CS_AKT_PROD_TB |     1 |       |     1   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------

    Outline Data
    -------------

      /*+
          BEGIN_OUTLINE_DATA
          IGNORE_OPTIM_EMBEDDED_HINTS
          OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
          DB_VERSION('12.1.0.2')
          OPT_PARAM('_optimizer_max_permutations' 50)
          OPT_PARAM('_unnest_subquery' 'false')
          OPT_PARAM('optimizer_dynamic_sampling' 4)
          OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
          OPT_PARAM('_optimizer_use_feedback' 'false')
          OPT_PARAM('_px_adaptive_dist_method' 'off')
          OPT_PARAM('_optimizer_dsdir_usage_control' 0)
          OPT_PARAM('_optimizer_adaptive_plans' 'false')
          OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
          OPT_PARAM('_optimizer_gather_feedback' 'false')
          OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
          OPT_PARAM('optimizer_index_cost_adj' 20)
          ALL_ROWS
          OUTLINE_LEAF(@"SEL$1")
          INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("PS_CS_AKT_PROD_TB"."BUSINESS_UNIT" 
                  "PS_CS_AKT_PROD_TB"."RA_CMPGN_WAVE_ID" "PS_CS_AKT_PROD_TB"."PRODUCT_ID" 
                  "PS_CS_AKT_PROD_TB"."CS_ABOART_CD"))
          END_OUTLINE_DATA
      */

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

       2 - access("A"."BUSINESS_UNIT"=:1 AND "A"."RA_CMPGN_WAVE_ID"=:2 AND 
                  "A"."PRODUCT_ID"=:3 AND "A"."CS_ABOART_CD"=:4)

Answer 1

Values of PRODUCT_ID field are unequaly distributed (see list of distribution in my question above).

Therefore in some rare cases, both plans are nearly equal. Because the statement is called with bind variables, the estimation is processed only at the first execution.

With

alter session set "_optim_peek_user_binds"=false;

the optimizer is forced to reevaluate the bind values at second execution.