摘要生产环境经常出现索引存在但SQL不走索引、强制全表扫描的诡异性能问题绝大多数并非索引损坏而是SQL写法不规范、数据倾斜、统计信息陈旧导致。本文结合 Oracle 19c 生产实战汇总索引失效完整场景、底层原理、可落地修复方案同时补充统计信息收集带来的临时表空间、归档日志资源影响及运维管控方案适合开发与DBA直接复用。关键词Oracle索引失效、全表扫描、隐式类型转换、统计信息过时、dbms_stats、Oracle19c、临时表空间、归档日志清理一、前言在Oracle SQL调优工作中索引失效是高频疑难问题。很多时候索引状态为VALID、结构完好但执行计划依旧是全表扫描最终导致接口超时、数据库CPU飙升、业务卡顿。生产经验90%的索引失效都是逻辑问题而非物理损坏。其中大表数据变更后统计信息过时是线上最高频诱因。本文系统化梳理失效场景并给出19c生产稳定修复脚本与资源风险规避方案。二、Oracle索引失效九大核心原因生产全覆盖1. 索引列使用函数/四则运算最高频失效原理B树索引存储的是字段原始值若对索引列做函数、运算处理索引无法匹配优化器直接放弃索引走全表扫描。错误示例sqlSELECT * FROM yb_si_reginfo WHERE TRUNC(create_time) 2026-07-07;SELECT * FROM yb_si_reginfo WHERE id 1 10086;优化方案将函数、运算逻辑转移至常量侧高频场景可建立函数索引。2. 隐式类型转换最隐蔽坑失效原理查询条件值类型与字段类型不一致Oracle自动触发隐式转换等价于对索引列施加函数计算索引直接失效。高频错误VARCHAR2字段传入数字where phone 13800138000NUMBER字段传入字符串where user_id 1001优化方案严格保持字段与查询值类型一致字符串条件必须加单引号。3. 左模糊/全模糊 LIKE 查询B树索引仅支持前缀匹配左模糊、全模糊无法走B树索引。失效like %xxx、like %xxx%生效like xxx%优化方案模糊检索业务可采用全文索引替代普通B树索引。4. 、!、NOT IN、IS NULL 条件B树索引不存储NULL值is null无法走普通索引不等值、NOT IN筛选度极低优化器判定全表扫描代价更低主动弃用索引。5. OR跨多索引列/无索引列OR连接多个不同索引列、或包含无索引字段时单条索引无法覆盖所有条件最终索引失效、触发全表扫描。6. 数据倾斜导致优化器主动弃用索引字段数据分布极度不均衡如99%数据为同一状态查询返回大量数据时回表代价远高于全表扫描CBO优化器会主动放弃索引属于正常优化行为非故障。7. 统计信息过时生产最高频修复场景大表批量增删改、数据迁移后表与索引的统计信息未更新优化器获取的行数、选择性、数据分布均为旧数据生成错误执行计划出现“有索引不走”的现象。8. 索引状态异常 UNUSABLE/DISABLE表移动、分区重组、索引重建中断等操作会导致索引状态变为UNUSABLE索引直接失效无法被SQL调用。9. 回表代价过高放弃索引非覆盖索引执行SELECT *需要大量回表查询数据数据返回量大时IO开销远超全表扫描优化器自动舍弃索引。三、生产快速排查索引失效步骤1. 检查索引状态是否正常sqlSELECT index_name,status FROM user_indexes WHERE table_name YB_SI_REGINFO;VALID索引正常UNUSABLE索引失效需要重建。2. 查看执行计划确认是否走全表扫描sqlEXPLAIN PLAN FOR 你的业务SQL;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());出现TABLE ACCESS FULL代表索引未生效走全表扫描。四、19c生产修复统计信息收集落地脚本针对统计信息过时导致的索引失效以下为生产稳定复用脚本适配Oracle19c可修复绝大部分索引不走、执行计划异常问题。sqlbegindbms_stats.gather_table_stats(ownname portal56_his,tabname yb_si_reginfo,estimate_percent dbms_stats.auto_sample_size,cascade true,degree 2,method_opt FOR ALL COLUMNS SIZE AUTO);end;/参数释义auto_sample_sizeOracle自动适配采样比例平衡精度与执行效率cascadetrue同步更新表、索引的统计信息degree2轻度并行提升大表执行速度避免资源抢占FOR ALL COLUMNS SIZE AUTO自动生成直方图解决数据倾斜导致的执行计划误判。五、重点进阶统计信息收集的生产资源风险很多开发只知道该脚本能修复索引失效却不清楚其会消耗临时表空间、REDO/归档日志高峰期执行极易引发生产故障。1. 对临时表空间的影响统计信息采样、分组计算、直方图生成会产生大量临时排序数据占用TEMP临时表空间。大表高并行执行时会快速打满临时空间触发ORA-01652 临时表空间不足导致统计收集失败、业务SQL报错。2. 对重做日志与归档日志的影响dbms_stats 属于DDL操作会持续生成REDO日志并触发归档。普通表影响极小TB级大表、批量更新统计时会短时间产生大量归档日志占用磁盘IO与磁盘空间引发磁盘告警。3. 生产执行硬性注意事项禁止业务高峰期执行避免抢占TEMP空间与IO资源超大表并行度建议1~2禁止开高并行统一使用 auto_sample_size禁止全表采样执行前必须检查临时表空间、归档磁盘余量分区表仅更新变更分区统计无需全表重复收集。六、Oracle19c专属运维核查脚本无废弃字段、零报错重要说明Oracle 12c/19c 废弃了11g大量旧字段dba_tablespaces.bytes、v$archived_log.bytes等网上多数旧脚本在19c直接报标识符无效以下为19c官方兼容终版脚本。6.1 19c查询临时表空间使用率执行统计收集前必查sql-- Oracle19c 稳定版 临时表空间使用率查询SELECTtu.tablespace_name 表空间名,dt.status 状态,ROUND(tu.tablespace_size * dt.block_size / 1024 / 1024, 2) 总大小_M,ROUND(tu.used_space * dt.block_size / 1024 / 1024, 2) 已使用_M,ROUND((tu.tablespace_size - tu.used_space) * dt.block_size / 1024 / 1024, 2) 剩余大小_M,ROUND(tu.used_percent, 2) 使用率百分比FROMdba_tablespace_usage_metrics tuJOINdba_tablespaces dt ON tu.tablespace_name dt.tablespace_nameWHEREtu.tablespace_name TEMP;6.2 19c查询归档日志大小及时间兼容无bytes字段sql-- Oracle19c 查看归档日志列表及大小-- 19c废弃bytes使用 blocks * block_size 精准计算SELECTsequence#,name,ROUND(blocks * block_size / 1024 / 1024,2) file_size_m,first_timeFROM v$archived_logWHERE statusAORDER BY first_time DESC;6.3 生产安全清理归档日志RMAN标准方案严禁操作系统直接删文件会导致控制文件与备份记录错乱数据库异常。生产统一使用RMAN清理sqlrman target /# 删除7天前的归档日志DELETE ARCHIVELOG ALL COMPLETED BEFORE SYSDATE-7;# 删除已备份过的归档日志DELETE BACKED UP 1 TIMES TO DEVICE TYPE DISK ARCHIVELOG ALL;# 校验并清理失效归档记录CROSSCHECK ARCHIVELOG;DELETE NOPROMPT EXPIRED ARCHIVELOG;6.4 运维最佳实践TEMP临时表空间使用率高于80%时禁止执行大表统计收集归档日志配置定时清理策略避免批量运维操作叠加日志暴涨所有统计更新、日志清理均在业务低峰期执行。七、总结Oracle索引失效核心诱因集中在SQL不规范、隐式转换、数据倾斜、统计信息过时。日常调优无需盲目重建索引优先排查SQL写法、索引状态、执行计划与统计信息。在19c生产中使用标准的dbms_stats脚本可快速修复绝大多数索引失效问题同时必须管控临时表空间与归档日志资源避免调优操作反而引发生产故障。原创不易点赞收藏持续分享Oracle生产调优实战干货