Oracle数据库中两张相似表执行计划差异问题排查咨询

现有两张结构完全一致（仅表名不同）的数据库表，索引配置也匹配，已重新收集两表统计信息并重建索引。两表仅数据存在差异（行数、行值、字段不同值数量等），每日均有大量INSERT/UPDATE/DELETE操作。执行相同SELECT查询时，正常表采用INDEX RANGE SCAN（耗时约1秒），异常表却采用INDEX FULL SCAN（耗时1-3分钟），且异常表此前无此问题。需排查以下内容以定位根因：

执行计划对比 #

正常表（SABA_MESSAGES）执行计划 #

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                              |     1 |  5833 |     4   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                         |                              |       |       |            |          |
|   2 |   VIEW                                 |                              |     1 |  5833 |     4   (0)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY               |                              |     1 |  1173 |     4   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED| SABA_MESSAGES                |     1 |  1173 |     4   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN                  | SABA_IDX_MESSAGES_PROCESS_ID |     1 |       |     3   (0)| 00:00:01 |

异常表（MESSAGES）执行计划 #

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     1 |  5833 |    23   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |             |       |       |            |          |
|   2 |   VIEW                        |             |     2 | 11666 |    23   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| MESSAGES    |   108K|   104M|    23   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | MESSAGES_PK |   124 |       |     3   (0)| 00:00:01 |

索引配置（两表结构一致，仅表名前缀不同） #

• MESSAGES表：
  • MESSAGES_PK：主键索引，基于ID字段
  • IDX_MESSAGES_PROCESS_ID：基于PROCESS_ID字段的普通索引
  • IDX_MESSAGES_MESSAGE_TYPE：基于MESSAGE_TYPE字段的普通索引
• SABA_MESSAGES表：
  • SABA_MESSAGES_PK：主键索引，基于ID字段
  • SABA_IDX_MESSAGES_PROCESS_ID：基于PROCESS_ID字段的普通索引
  • SABA_IDX_MESSAGES_MESSAGE_TYPE：基于MESSAGE_TYPE字段的普通索引

表统计信息 #

需排查的核心内容 #

• 验证统计信息准确性：
  确认统计信息收集参数是否合理（如采样率设为自动），是否收集了PROCESS_ID字段的直方图。对比两张表PROCESS_ID的NUM_DISTINCT、直方图类型等统计值，避免因统计信息失真导致CBO误判。
• 检查索引统计与有效性：
  查看异常表IDX_MESSAGES_PROCESS_ID索引的统计信息（如DISTINCT_KEYS、LEAF_BLOCKS、CLUSTERING_FACTOR），对比正常表对应索引的数值。确认索引无损坏、无锁定，重建时参数正确（如ALTER INDEX ... REBUILD ONLINE）。
• 分析查询谓词的数据分布：
  检查SQL中PROCESS_ID的查询值在两张表中的实际行数：若异常表中该值对应的行数占比极高（如超过全表30%），CBO可能认为全索引扫描成本更低。同时排查是否存在绑定变量窥探，导致复用了不符合当前数据分布的旧计划。
• 排查表与索引碎片：
  检查异常表的表碎片（如DBA_TABLES中的CHAIN_CNT、AVG_SPACE）和索引碎片（DBA_INDEXES中的BLEVEL、LEAF_BLOCKS），高碎片可能影响CBO的成本计算逻辑。
• 核对CBO参数与执行计划绑定：
  检查会话或系统级CBO参数（如OPTIMIZER_MODE、OPTIMIZER_INDEX_COST_ADJ）是否存在差异。确认异常表的SQL是否被绑定了SQL Profile、SQL Patch或基线，强制使用全索引扫描计划。
• 关联历史执行计划与DML操作：
  查询异常表对应SQL的历史执行计划（如通过DBA_HIST_SQL_PLAN），定位执行计划发生变化的时间点，关联该时段的批量DML操作（如批量插入/更新PROCESS_ID字段），确认是否因数据分布突变导致CBO决策变化。

内容的提问来源于stack exchange，提问作者Coy