Oracle 12c删除分区后主键索引失效执行计划走全表扫描问题

可能原因 #

• 统计信息失真：分区删除操作后仅收集了单表统计信息，缺失关联列的直方图、跨表关联统计信息，或统计信息采样率不足导致优化器对join成本估算错误，判定全表扫描成本低于索引扫描
• 索引属性异常：XPKFSC_ACCOUNT_DIM被设置为不可见（INVISIBLE），或主键约束被标记为DISABLE/NOVALIDATE，优化器默认不会选择不可见索引
• 优化器参数配置偏差：test环境optimizer_index_cost_adj、db_file_multiblock_read_count参数被修改，前者默认值为100，数值越高优化器对索引扫描的成本估算越高，越倾向选择全表扫描
• 连接顺序估算错误：test环境事实表fsc_cash_flow_fact数据量远大于dev环境，优化器错误判定优先扫描维度表做hash join的成本更低，放弃走索引嵌套循环连接

排查步骤 #

1. 首先检查索引基础状态
   执行以下查询确认索引状态和可见性：

select status, visibility from dba_indexes 
where index_name = 'XPKFSC_ACCOUNT_DIM' and owner = 'FCFCORE';

正常返回结果应为STATUS=VALID，VISIBLE=VISIBLE。

2. 检查优化器相关参数配置
   对比dev和test环境的核心优化器参数是否一致：

select name, value from v$parameter 
where name in ('optimizer_index_cost_adj', 'db_file_multiblock_read_count', 'optimizer_mode');

3. 验证关联列统计信息准确性
   对比两个环境中account_key列的统计信息是否匹配：

-- 查事实表account_key统计
select num_distinct, num_nulls, histogram from dba_tab_col_statistics
where owner = 'FCFCORE' and table_name = 'FSC_CASH_FLOW_FACT' and column_name = 'ACCOUNT_KEY';

-- 查维度表account_key统计
select num_distinct, num_nulls, histogram from dba_tab_col_statistics
where owner = 'FCFCORE' and table_name = 'FSC_ACCOUNT_DIM' and column_name = 'ACCOUNT_KEY';

若数值偏差超过20%，说明统计信息失真。

4. 检查执行计划的连接顺序和成本估算
   获取test环境查询的10053 trace日志，查看优化器对索引扫描的成本估算值，确认是否存在成本计算偏差。

解决方法 #

• 若索引可见性异常，执行以下语句修改：

alter index FCFCORE.XPKFSC_ACCOUNT_DIM visible;

• 若参数配置偏差，调整回和dev环境一致的参数值，会话级测试验证：

alter session set optimizer_index_cost_adj = 100;

• 若统计信息失真，用高采样率重新收集两张表的统计信息，同时收集关联列的扩展统计信息：

exec dbms_stats.gather_table_stats(ownname => 'FCFCORE', tabname => 'FSC_CASH_FLOW_FACT', estimate_percent => 30, cascade => true, no_invalidate => false);
exec dbms_stats.gather_table_stats(ownname => 'FCFCORE', tabname => 'FSC_ACCOUNT_DIM', estimate_percent => 100, cascade => true, no_invalidate => false);

• 若依然无效，可以锁定dev环境的执行计划，导入到test环境中，无需修改业务SQL加hint。

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