Oracle一次“选错索引”问题的分析

select  ……        reqAmount,        bizExp,        gjmerno   from T_PAYMB T  where MERREFUNDNO = :1    AND MERNO =  :2    AND 1 = 1     该表数据量900万，按月分区。 MERREFUNDNO字段上有本地索引（下文称索引1），基本可以唯一区分数据，长度较长； MERNO字段上有全局索引（下文称索引2），区分度不高，长度较短。 今天多次出现使用MERNO字段索引，耗时超过一分钟才完成。 首先查看该表及两个索引的统计信息，并没有特别离谱。 下面通过10053事件分析： *************************************** BASE STATISTICAL INFORMATION *********************** ……   Index: IDX_MERREFUND_MERNO  Col#: 2   LVLS: 2  #LB: 44739  #DK: 51732  LB/K: 1.00  DB/K: 31.00  CLUF: 1641298.00  NRW: 8931001.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 8192 BSZ: 1   KKEISFLG: 1    Index: IDX_MERREFUND_MREREFUNDNO  Col#: 18     USING COMPOSITE STATS   LVLS: 2  #LB: 56666  #DK: 8903680  LB/K: 1.00  DB/K: 1.00  CLUF: 7767623.00  NRW: 8911691.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 8192 BSZ: 1   KKEISFLG: 1      ALL PARTITIONS USABLE ……

字段含义：

Access path analysis for T_PAYMB *************************************** SINGLE TABLE ACCESS PATH    Single Table Cardinality Estimation for T_PAYMB[T]    SPD: Directive valid: dirid = 11567766291776946222, state = 1, flags = 1, loc = 1, forDS = NO, forCG = YES {EC(237427)[2]}   SPD: Return code in qosdDSDirSetup: EXISTS, estType = TABLE   Column (#18): MERREFUNDNO(VARCHAR2)     AvgLen: 22 NDV: 8903680 Nulls: 19308 Density: 0.000000   Estimated selectivity: 1.1231e-07 , col: #18    Column (#2):      NewDensity:0.000003, OldDensity:0.000003 BktCnt:5533.000000, PopBktCnt:4736.000000, PopValCnt:9, NDV:51732   Column (#2): MERNO(VARCHAR2)     AvgLen: 16 NDV: 51732 Nulls: 0 Density: 0.000003     Histogram: Hybrid  #Bkts: 254  UncompBkts: 5533  EndPtVals: 254  ActualVal: yes   Using density: 2.7849e-06 of col #2 as selectivity of pred having unreasonably low value  kkecdn: Single Table Predicate:"T"."MERREFUNDNO"='20211207000000070033'   Estimated selectivity: 1.1231e-07 , col: #18   kkecdn: Single Table Predicate:"T"."MERNO"='301100710000036'   Using density: 2.7849e-06 of col #2 as selectivity of pred having unreasonably low value   Table: T_PAYMB  Alias: T     Card: Original: 8930996.000000  Rounded: 1  Computed: 0.000003  Non Adjusted: 0.000003   Scan IO  Cost (Disk) =   118240.000000   Scan CPU Cost (Disk) =   7485209923.680000   Cost of predicates:     io = NOCOST, cpu = 50.000000, sel = 0.000000 flag = 2048  ("T"."MERREFUNDNO"='20211207000000070033')     io = NOCOST, cpu = 50.000000, sel = 0.000003 flag = 2048  ("T"."MERNO"='301100710000036')   Total Scan IO  Cost  =   118240.000000 (scan (Disk))                          + 0.000000 (io filter eval) (= 0.000000 (per row) * 8930996.000000 (#rows))                        =   118240.000000   Total Scan CPU  Cost =   7485209923.680000 (scan (Disk))                          + 446549850.153397 (cpu filter eval) (= 50.000006 (per row) * 8930996.000000 (#rows))                        =   7931759773.833398   Access Path: TableScan     Cost:  118557.786130  Resp: 118557.786130  Degree: 0       Cost_io: 118240.000000  Cost_cpu: 7931759774       Resp_io: 118240.000000  Resp_cpu: 7931759774  ****** Costing Index IDX_MERREFUND_MERNO   SPD: Directive valid: dirid = 11567766291776946222, state = 1, flags = 1, loc = 1, forDS = NO, forCG = YES {EC(237427)[2]}   SPD: Return code in qosdDSDirSetup: EXISTS, estType = INDEX_SCAN   SPD: Directive valid: dirid = 11567766291776946222, state = 1, flags = 1, loc = 1, forDS = NO, forCG = YES {EC(237427)[2]}   SPD: Return code in qosdDSDirSetup: EXISTS, estType = INDEX_FILTER   Using density: 2.7849e-06 of col #2 as selectivity of pred having unreasonably low value   Access Path: index (AllEqRange)     Index: IDX_MERREFUND_MERNO     resc_io: 8.000000  resc_cpu: 89965     ix_sel: 2.7849e-06  ix_sel_with_filters: 2.7849e-06      Cost: 8.003604  Resp: 8.003604  Degree: 1 》》》》》》》》》》》》》》》》》》优化器评估使用MERNO字段的索引进行range scan时cost值为8  ****** Costing Index IDX_MERREFUND_MREREFUNDNO   SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_SCAN   SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_FILTER   Estimated selectivity: 1.1231e-07 , col: #18    Access Path: index (AllEqRange)     Index: IDX_MERREFUND_MREREFUNDNO     resc_io: 58.000000  resc_cpu: 415634     ix_sel: 1.1231e-07  ix_sel_with_filters: 1.1231e-07      Cost: 58.016652  Resp: 58.016652  Degree: 1 》》》》》》》》》》》》》》》》》》优化器评估使用MERREFUNDNO字段的索引进行range scan时cost值为58           ****** trying bitmap/domain indexes ******   Using density: 2.7849e-06 of col #2 as selectivity of pred having unreasonably low value  ****** Costing Index IDX_MERREFUND_MERNO   SPD: Directive valid: dirid = 11567766291776946222, state = 1, flags = 1, loc = 1, forDS = NO, forCG = YES {EC(237427)[2]}   SPD: Return code in qosdDSDirSetup: EXISTS, estType = INDEX_SCAN   SPD: Directive valid: dirid = 11567766291776946222, state = 1, flags = 1, loc = 1, forDS = NO, forCG = YES {EC(237427)[2]}   SPD: Return code in qosdDSDirSetup: EXISTS, estType = INDEX_FILTER   Using density: 2.7849e-06 of col #2 as selectivity of pred having unreasonably low value   Access Path: index (AllEqRange)     Index: IDX_MERREFUND_MERNO     resc_io: 3.000000  resc_cpu: 26364     ix_sel: 2.7849e-06  ix_sel_with_filters: 2.7849e-06      Cost: 3.001056  Resp: 3.001056  Degree: 0   Estimated selectivity: 1.1231e-07 , col: #18   ****** Costing Index IDX_MERREFUND_MREREFUNDNO   SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_SCAN   SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_FILTER   Estimated selectivity: 1.1231e-07 , col: #18    Access Path: index (AllEqRange)     Index: IDX_MERREFUND_MREREFUNDNO     resc_io: 57.000000  resc_cpu: 406322     ix_sel: 1.1231e-07  ix_sel_with_filters: 1.1231e-07      Cost: 57.016279  Resp: 57.016279  Degree: 0   Bitmap nodes:     Used IDX_MERREFUND_MERNO       Cost = 3.001191, sel = 2.7849e-06     Used IDX_MERREFUND_MREREFUNDNO       Cost = 57.016285, sel = 1.1207e-07 》》》》》》》》》》》》》》》》》》优化器评估使用MERNO\MERREFUNDNO字段的索引,并进行bitmap运算时cost值为3+57=60                ****** finished trying bitmap/domain indexes ******   Best:: AccessPath: IndexRange   Index: IDX_MERREFUND_MERNO          Cost: 8.003604  Degree: 1  Resp: 8.003604  Card: 0.000003  Bytes: 0.000000           最终选择了MERNO字段的索引。 MERNO字段数据倾斜比较严重，按MERNO进行分区统计，最多的超过300万，最少为1。 查看真实执行计划，发现预估的行数与实际还是有较大差距的。 ----------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                                  | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers | ----------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                           |                      |      1 |        |      1 |00:00:00.01 |    1053 | |*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T_PAYMB              |      1 |      1 |      1 |00:00:00.01 |    1053 | |*  2 |   INDEX RANGE SCAN                         | IDX_MERREFUND_MERNO  |      1 |     25 |   1205 |00:00:00.01 |      13 | ----------------------------------------------------------------------------------------------------------------------------- 最后使用sql profile对执行计划进行了固化。