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對執行計劃進行了固化。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-2846252/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 關於oracle的索引重建問題及原因分析Oracle索引
- Oracle建立索引選擇合適的可選項及效率問題Oracle索引
- Oracle 索引的三個問題(轉)Oracle索引
- MySQL 選錯索引的原因？MySql索引
- 資料庫索引分裂 問題分析資料庫索引
- 一次線上OOM問題分析OOM
- 一次inmemory丟失引起的問題分析
- oracle 索引分析及索引重建Oracle索引
- Oracle表與索引的分析及索引重建Oracle索引
- 一次資料庫當機問題的分析資料庫
- 一次library cache lock 問題分析
- Oracle表與索引的分析及索引重建(轉）Oracle索引
- 一次oracle 節點重啟問題的定位Oracle
- 一次oracle行級鎖導致的問題Oracle
- Oracle 外來鍵索引影響阻塞問題Oracle索引
- 函式索引的問題函式索引
- 一次線上 CPU 飆升問題的分析解決
- Oracle對索引分析的優化Oracle索引優化
- oracle 表分析和索引Oracle索引
- Oracle全部索引丟失導致的效率問題處理Oracle索引
- 記一次線上報錯日誌問題排查
- 不走索引場景的一次分析優化索引優化
- 一次TiDB GC阻塞引發的效能問題分析TiDBGC
- 一次「找回」TraceId的問題分析與過程思考
- 一次ORA-00600問題的排查和分析(上）
- 一次ORA-00600問題的排查和分析(下）
- navicate oracle 連線報錯問題Oracle
- kk系統索引的問題索引
- Oracle對錶、索引和簇的分析Oracle索引
- 索引在ORACLE中的應用分析索引Oracle
- ORACLE分析表和索引的指令碼Oracle索引指令碼
- 記一次crontab中date命令錯用導致的問題
- Oracle 索引訪問方式Oracle索引
- oracle交換分割槽所引起的索引失效問題探究測試Oracle索引
- 解決Oracle11g中的索引名字亂碼問題Oracle索引
- 有關Oracle Index 的三個問題－索引也有好壞(轉)OracleIndex索引
- ORACLE 組合索引 使用分析Oracle索引
- oracle 定期表及索引分析Oracle索引