重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

标题: oracle数据库索引掉了与失效的排查办法 [打印本页]

作者: mahan 时间: 7 天前
标题: oracle数据库索引掉了与失效的排查办法
但在具体排查之前，需要先明确你所说的“索引掉了”具体是指哪一种情况？

在 Oracle 数据库中，通常有以下三种情况会被大家称为“索引掉了”：

物理删除：索引被彻底删除了（执行了 DROP INDEX）。

状态失效：索引还在数据库里，但状态变成了不可用（UNUSABLE）。

执行计划改变（不走索引）：索引完好无损，但是 SQL 查询突然不再使用这个索引了（全表扫描）。

针对这三种情况，排查方法各不相同：

情况一：索引被物理删除了 (DROP INDEX)
如果有人或程序误删了索引，你可以通过以下几种方式来追查“凶手”和时间：

查看回收站 (Recycle Bin)
如果数据库开启了回收站，且删除时没有加 PURGE，被删除的索引会留在回收站中。

SQL
SELECT OBJECT_NAME, ORIGINAL_NAME, DROPTIME
FROM DBA_RECYCLEBIN
WHERE TYPE = 'INDEX' AND ORIGINAL_NAME = '你的索引名';
查看审计日志 (Audit Trail)
如果数据库开启了 DDL 审计（这是排查最准确的方法），可以查到是谁、在什么时间、从哪台机器执行了删除操作。

SQL
SELECT USERNAME, OS_USERNAME, TERMINAL, TIMESTAMP, SQL_TEXT
FROM DBA_AUDIT_TRAIL
WHERE ACTION_NAME = 'DROP INDEX' AND OBJ_NAME = '你的索引名';
查询历史会话 (ASH / AWR)
如果删除操作发生在近期，可以通过 ASH 视图查找曾经执行过 DROP 语句的会话记录。

SQL
SELECT SAMPLE_TIME, USER_ID, SQL_OPCODE, SQL_TEXT
FROM DBA_HIST_ACTIVE_SESS_HISTORY h
JOIN DBA_HIST_SQLTEXT t ON h.SQL_ID = t.SQL_ID
WHERE UPPER(t.SQL_TEXT) LIKE '%DROP INDEX%你的索引名%';
使用 LogMiner 挖掘日志
如果没有开启审计，且历史记录已被冲掉，最后的杀手锏是使用 LogMiner 去解析 Oracle 的归档日志 (Archive Logs)。这可以 100% 还原出执行 DROP INDEX 的准确时间和相关事务，但操作相对复杂。

情况二：索引状态失效 (UNUSABLE)
如果索引还在，但是查询报错或者变慢，可能是索引失效了。

1. 确认索引状态：

SQL
SELECT INDEX_NAME, STATUS, LAST_ANALYZED
FROM DBA_INDEXES
WHERE INDEX_NAME = '你的索引名';
如果 STATUS 显示为 UNUSABLE，说明索引失效。

2. 常见原因排查：

表结构维护操作：最近是否有人对表执行了 ALTER TABLE ... MOVE 改变了数据的物理存储位置？（这会导致所有普通索引失效）。

分区表操作：如果是分区表，近期是否执行了 DROP PARTITION、TRUNCATE PARTITION 或 SPLIT PARTITION，且没有加上 UPDATE GLOBAL INDEXES 子句？（这会导致全局索引失效）。

直接路径加载：是否使用了 SQL*Loader 的直接路径加载，或者执行了 INSERT /*+ APPEND */ ... 且中间发生了异常中断？

解决办法：
重建索引即可恢复：ALTER INDEX 你的索引名 REBUILD; （如果是分区索引可能需要 REBUILD PARTITION）。

情况三：SQL 突然不走索引了（执行计划改变）
这是最常见的“性能故障”，通常不是索引坏了，而是 Oracle 的优化器 (CBO) “认为”不走索引更快。

常见原因排查：

统计信息过期或突变：
表的数据量是否发生了剧增或大批量删除？使用以下语句查看表和索引最近一次收集统计信息的时间：

SQL
SELECT TABLE_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME = '你的表名';
SELECT INDEX_NAME, LAST_ANALYZED FROM DBA_INDEXES WHERE TABLE_NAME = '你的表名';
解决思路：重新收集统计信息 EXEC DBMS_STATS.GATHER_TABLE_STATS(...)
2. 隐式类型转换：
比如你的索引列是 VARCHAR2 类型，但是 SQL 里写的是 WHERE col = 123（传了数字类型）。Oracle 会在内部套用 TO_NUMBER() 函数，导致索引失效。
3. 在索引列上使用了函数：
如 WHERE TRUNC(create_time) = SYSDATE，除非你建了基于函数的索引，否则普通索引会失效。
4. 数据倾斜与绑定变量窥探 (Bind Peeking)：
某个特定值的查询返回了表中大部分数据（比如状态位查询），Oracle 在第一次硬解析时“窥探”到了这个值，认为走全表扫描成本更低，并把这个执行计划缓存了下来，导致后续查小量数据的变量也被迫走全表扫描。

总结建议
你可以先执行一句简单的查询确认现状：

SQL
SELECT INDEX_NAME, INDEX_TYPE, STATUS
FROM USER_INDEXES
WHERE TABLE_NAME = '你的表名';
先看看它在不在，再看看它有没有失效 (STATUS)。

欢迎光临重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)