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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL 選錯索引的原因？MySql索引
- MySQL 選錯索引MySql索引
- 資料庫索引分裂 問題分析資料庫索引
- 索引選擇度問題最佳化整理索引
- 一次inmemory丟失引起的問題分析
- 記一次HttpClient使用問題分析HTTPclient
- 一次線上OOM問題分析OOM
- 一次library cache lock 問題分析
- oracle交換分割槽所引起的索引失效問題探究測試Oracle索引
- Oracle 記一次ORA-00001問題處理Oracle
- oracle的索引Oracle索引
- 一次「找回」TraceId的問題分析與過程思考
- 一次TiDB GC阻塞引發的效能問題分析TiDBGC
- 一次詭異的Oracle使用者無法su問題Oracle
- Matlab轉python的索引問題MatlabPython索引
- Oracle Rman多通道故障轉移問題分析Oracle
- Oracle 19C RAC腦裂問題分析Oracle
- Oracle 錯誤總結及問題解決 ORAOracle
- 【ASK_ORACLE】由於索引分裂而產生的效能問題的解決方案Oracle索引
- 記一次crontab中date命令錯用導致的問題
- 好文分享 | 記一次Oracle12c資料庫SQL短暫緩慢問題分析Oracle資料庫SQL
- Oracle資料不同步的問題分析和解決思路Oracle
- 記一次報錯 symlink(): Protocol error 問題處理ProtocolError
- 記一次線上報錯日誌問題排查
- 記一次Oracle分割槽表全域性索引重建的過程Oracle索引
- Oracle 惡意攻擊問題分析和解決(一)Oracle
- LightDB Canopy 常見報錯問題分析（一）
- Oracle優化案例-join列索引缺失導致的sql效能問題（二十六）Oracle優化索引SQL
- Oracle 索引Oracle索引
- Mysql索引失效問題demoMySql索引
- Oracle 條件索引 case when 報錯解決方案Oracle索引
- Oracle trigger問題Oracle
- 聊一聊MySQL索引失效的問題MySql索引
- 【Mongo】mongos shard 唯一索引的問題Go索引
- Oracle的全文索引Oracle索引
- 【ERROR】儲存鏈路問題造成oracle錯誤，ora-600[4193] 問題處理ErrorOracle
- 不能建立降序索引的問題的解決索引
- Oracle索引梳理系列（六）- Oracle索引種類之函式索引Oracle索引函式