oracle - 优化器使用错误的索引

Question

我们有一个非常简单的选择语句来访问具有唯一索引字段的数据。然而，优化器决定使用一个错误的索引，选择需要大约 4 秒而不是 0.0x 秒。

这个问题在 oracle 11g 中不存在，但在 oracle 12c 中。

表统计信息是最新的。

看来，对坏计划的估计是错误的(见下文)，我们怎样才能避免这种情况呢？

我想通过添加字段统计信息或基线条目，但我希望有另一种解决方案。

提前致谢。

表定义

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);

索引

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);

表大小

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

选择声明

给出了唯一索引的所有必填字段:

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;

执行细节和解释计划

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)

    */    

修正(见评论)

PROCUCT_ID 的字段值分布不均

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

dbms_xplan.display_cursor(null, null, '+OUTLINE') 的输出

长达 37 秒:

    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 秒

    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

PRODUCT_ID 的值字段分布不均(请参阅上面我的问题中的分布列表)。

因此，在极少数情况下，两个计划几乎相等。因为该语句是使用绑定(bind)变量调用的，所以仅在第一次执行时处理估计。

和

alter session set "_optim_peek_user_binds"=false;

优化器被迫在第二次执行时重新评估绑定(bind)值。