印刻数据库

外观

Oracle 索引失效场景 ​

索引失效概述 ​

索引失效是 Oracle 数据库中常见的性能问题，指索引无法被优化器有效使用，导致查询性能下降。索引失效可能由多种原因引起，包括人为操作、系统设置或数据库内部机制等。理解索引失效的场景和处理方法，对于 DBA 维护数据库性能至关重要。

索引失效的原因 ​

1. 人为操作导致的失效 ​

  • 并行度设置：为索引设置了并行度后未及时恢复
  • 索引重建/重建：重建索引后并行度未重置
  • 分区操作：分区维护后并行度遗留

2. 数据库内部机制导致的失效 ​

  • 统计信息过时：索引统计信息不准确或过时
  • 索引碎片：频繁 DML 操作导致索引碎片化
  • 索引损坏：索引结构损坏或不一致

3. 索引设计问题 ​

  • 索引类型不匹配：索引类型与查询模式不匹配
  • 缺少必要索引：查询条件未被索引覆盖
  • 复合索引顺序不当：复合索引列顺序不合理

索引失效的识别方法 ​

1. 执行计划分析 ​

通过查看 SQL 执行计划，识别索引是否被使用：

sql
-- 查看执行计划
EXPLAIN PLAN FOR SELECT * FROM EMP WHERE deptno = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

如果执行计划中出现 TABLE ACCESS FULL 而不是预期的索引访问，说明索引可能失效。

2. 索引使用监控 ​

使用 Oracle 提供的监控视图和动态性能视图：

sql
-- 监控索引使用情况
SELECT * FROM V$SQL WHERE sql_text LIKE '%INDEX%';

-- 检查索引状态
SELECT index_name, status, leaf_blocks, num_rows FROM DBA_INDEXES WHERE index_name = 'IDX_EMP_DEPT';

3. 统计信息检查 ​

sql
-- 检查索引统计信息
EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname => 'SCOTT', indname => 'IDX_EMP_DEPT');

-- 查看统计信息状态
SELECT * FROM DBA_INDEXES WHERE index_name = 'IDX_EMP_DEPT';

常见索引失效场景及处理 ​

场景 1：索引并行度遗留 ​

现象：索引重建或统计收集时设置了并行度，操作完成后未重置，导致后续查询使用不当并行度。

处理方法：

sql
-- 重建索引时使用并行度加速
ALTER INDEX SCOTT.IDX_EMP_DEPT REBUILD PARALLEL 4;

-- 监控重建进度
SELECT * FROM V$INDEX_REBUILD_STATUS WHERE index_name = 'IDX_EMP_DEPT';

-- 重置并行度
ALTER INDEX SCOTT.IDX_EMP_DEPT NOPARALLEL;

场景 2：分区索引失效 ​

现象：分区表索引按季度分区，部分历史分区索引碎片化。

处理方法：

sql
-- 创建分区索引维护任务
CREATE OR REPLACE PROCEDURE MAINTAIN_PARTITION_INDEX IS
BEGIN
    -- 收集指定分区统计信息
    EXEC DBMS_STATS.GATHER_INDEX_STATS(
        ownname => 'SCOTT',
        indname => 'IDX_EMP_DEPT',
        degree => 1
    );
    
    -- 重置并行度
    EXECUTE IMMEDIATE 'ALTER INDEX SCOTT.IDX_EMP_DEPT NOPARALLEL';
END;
/

场景 3：自动维护分区索引 ​

现象：按月份自动维护分区索引统计信息。

处理方法：

sql
-- 创建维护任务
CREATE OR REPLACE PROCEDURE MAINTAIN_MONTHLY_PARTITION_INDEX AS
    v_partition_name VARCHAR2(30);
BEGIN
    -- 生成当前月份分区名称
    v_partition_name := 'SALES_' || TO_CHAR(SYSDATE, 'MON_YYYY');
    
    -- 收集新增分区统计信息
    EXEC DBMS_STATS.GATHER_INDEX_STATS(
        ownname => 'SCOTT',
        indname => 'IDX_SALES_DATE',
        partname => v_partition_name,
        degree => DBMS_STATS.AUTO_DEGREE
    );
    
    -- 重建碎片化分区索引（使用动态SQL）
    EXECUTE IMMEDIATE 'ALTER INDEX SCOTT.IDX_SALES_DATE REBUILD PARTITION ' || v_partition_name || ' PARALLEL 4';
    
    -- 重置并行度
    EXECUTE IMMEDIATE 'ALTER INDEX SCOTT.IDX_SALES_DATE MODIFY PARTITION ' || v_partition_name || ' NOPARALLEL';
END;
/

-- 调度任务
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'MAINTAIN_MONTHLY_PARTITION_INDEX_JOB',
        job_type => 'STORED_PROCEDURE',
        job_action => 'MAINTAIN_MONTHLY_PARTITION_INDEX',
        start_date => SYSTIMESTAMP,
        repeat_interval => 'FREQ=MONTHLY; BYDAY=1; BYHOUR=2; BYMINUTE=0; BYSECOND=0',
        enabled => TRUE,
        comments => '每月1日凌晨2点维护分区索引'
    );
END;
/

19c 和 21c 版本差异 ​

Oracle 19c 新特性 ​

  1. 自动索引：19c 引入自动索引管理
  2. 并行度增强：重建时支持更高并行度，重建后需手动重置
  3. 统计信息收集：支持增量收集，只收集变化分区的统计信息

Oracle 21c 新特性 ​

  1. 实时监控：V$INDEX_REBUILD_STATUS 视图增强
  2. 智能并行度：自动调整索引并行度
  3. 碎片自动修复：只修复碎片化索引
  4. 索引压缩：增强压缩算法

常见问题（FAQ） ​

Q1：如何判断索引是否需要重建？ ​

A：通过以下方法判断：

  • 索引高度超过 4 层
  • 索引碎片率超过 30%
  • 叶子块使用率低于 50%
  • 查询 V$INDEX_STATS 中 index_name 对应的值

Q2：分区索引需要全部重建吗？ ​

A：不需要，只重建碎片化的分区，使用 INCREMENTAL 选项只收集变化分区的统计信息。

Q3：索引维护后需要做什么？ ​

A：

  1. 重置并行度（避免资源浪费）
  2. 收集统计信息
  3. 验证索引状态

Q4：如何自动化索引维护？ ​

A：使用 Oracle Scheduler 创建定时任务，或使用 PL/SQL 存储过程结合 DBMS_SCHEDULER 自动执行。

总结 ​

索引失效是 Oracle 数据库维护中的常见问题，DBA 需要：

  1. 了解索引失效的原因
  2. 掌握识别方法
  3. 针对不同场景选择合适的处理策略
  4. 结合自动化工具提高效率
  5. 关注版本差异，选择合适的维护方法

通过合理的索引维护，可以有效提高数据库查询性能，减少资源消耗，确保系统稳定运行。