Oracle索引监控与优化实战指南

1. 索引监控的必要性与原理在Oracle数据库管理中索引是提升查询性能的关键因素但维护索引也需要付出存储空间和维护成本。根据统计生产环境中约20-30%的索引从未被使用过这些僵尸索引不仅占用存储空间还会降低DML操作INSERT/UPDATE/DELETE的效率。索引监控的核心原理是通过Oracle内置的监控机制记录索引被访问的情况。当开启监控后Oracle会在内存中维护一个使用状态标志每次索引被用于执行计划时更新这个标志。这种监控对性能影响极小官方文档显示额外开销小于1%适合在生产环境长期运行。注意索引监控功能需要ALTER ANY INDEX系统权限普通用户只能监控自己拥有的索引。对于企业级环境建议由DBA统一执行监控操作。2. 索引监控全流程操作指南2.1 开启索引监控以下PL/SQL脚本会为指定用户下的所有索引开启监控-- 替换YOUR_SCHEMA为实际用户名 BEGIN FOR idx IN (SELECT owner, index_name FROM dba_indexes WHERE owner YOUR_SCHEMA) LOOP EXECUTE IMMEDIATE ALTER INDEX || idx.owner || . || idx.index_name || MONITORING USAGE; END LOOP; COMMIT; DBMS_OUTPUT.PUT_LINE(已为 || SQL%ROWCOUNT || 个索引开启监控); END; /执行后可以通过以下查询验证监控状态SELECT index_name, monitoring, used FROM v$object_usage WHERE owner YOUR_SCHEMA;2.2 监控周期建议监控持续时间应根据业务特点确定OLTP系统建议至少覆盖1-2个完整的业务周期通常1-2周报表系统建议覆盖主要报表生成时段季节性业务需要覆盖业务高峰时段2.3 查询未使用索引监控一段时间后执行以下查询获取未使用索引清单SELECT u.owner, u.index_name, i.table_name, u.monitoring, u.used, u.start_monitoring, u.end_monitoring, i.index_type, i.uniqueness, i.status FROM v$object_usage u JOIN dba_indexes i ON u.index_name i.index_name AND u.owner i.owner WHERE u.owner YOUR_SCHEMA AND u.used NO ORDER BY i.table_name, u.index_name;2.4 关闭监控完成监控后应及时关闭以减少系统开销BEGIN FOR idx IN (SELECT owner, index_name FROM dba_indexes WHERE owner YOUR_SCHEMA) LOOP EXECUTE IMMEDIATE ALTER INDEX || idx.owner || . || idx.index_name || NOMONITORING USAGE; END LOOP; COMMIT; END; /3. 基于AWR的索引使用分析3.1 AWR索引使用统计查询对于拥有AWR许可的环境可以获取更全面的索引使用情况SELECT d.object_owner, d.object_name as index_name, t.table_name, COUNT(DISTINCT d.sql_id) as sql_count, COUNT(*) as plan_line_count, MIN(d.timestamp) as first_seen, MAX(d.timestamp) as last_seen FROM dba_hist_sql_plan d JOIN dba_indexes i ON d.object_owner i.owner AND d.object_name i.index_name JOIN dba_tables t ON i.table_owner t.owner AND i.table_name t.table_name WHERE d.object_owner YOUR_SCHEMA AND d.object_type INDEX AND d.timestamp SYSDATE - 30 -- 分析最近30天数据 GROUP BY d.object_owner, d.object_name, t.table_name ORDER BY sql_count ASC;关键字段说明sql_count使用该索引的SQL语句数量plan_line_count在执行计划中出现的总次数first_seen/last_seen首次和最近使用时间3.2 识别长期未使用的索引以下查询可找出90天内从未出现在AWR中的索引SELECT i.owner, i.index_name, i.table_name, i.index_type, i.uniqueness, i.status, i.num_rows, i.leaf_blocks FROM dba_indexes i LEFT JOIN ( SELECT DISTINCT object_owner, object_name FROM dba_hist_sql_plan WHERE object_type INDEX AND timestamp SYSDATE - 90 ) p ON i.owner p.object_owner AND i.index_name p.object_name WHERE i.owner YOUR_SCHEMA AND p.object_name IS NULL AND i.index_type NOT IN (LOB, DOMAIN) ORDER BY i.table_name, i.index_name;4. 索引优化实战建议4.1 索引删除决策流程发现未使用索引后建议按以下流程处理确认索引类型唯一索引(UNIQUE)通常不能删除检查外键约束可能有隐式创建的索引验证业务需求是否用于特殊时期或特定功能先在测试环境删除并验证生产环境删除前做好备份4.2 索引重建与合并对于使用频率低的索引可考虑重建减少存储空间ALTER INDEX ... REBUILD合并碎片ALTER INDEX ... COALESCE4.3 监控脚本自动化建议创建定期执行的监控作业BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name MONITOR_INDEX_USAGE, job_type PLSQL_BLOCK, job_action BEGIN -- 开启监控代码 -- 定期查询代码 END;, start_date SYSTIMESTAMP, repeat_interval FREQWEEKLY; BYDAYMON, enabled TRUE, comments Weekly index usage monitoring); END; /5. 常见问题与解决方案5.1 监控结果不准确的可能原因统计信息过时EXEC DBMS_STATS.GATHER_SCHEMA_STATS(YOUR_SCHEMA);SQL使用绑定变量导致无法匹配监控期间未执行典型业务5.2 特殊索引处理建议函数索引检查函数条件是否与查询匹配位图索引OLTP系统慎用分区索引检查所有分区使用情况5.3 性能优化案例某电商平台通过索引监控发现订单表上12个索引中有3个从未使用删除后DML操作性能提升15%节省存储空间约20GB6. 高级技巧与扩展分析6.1 使用SQL Tuning Advisor分析对使用频率低的索引可进一步分析DECLARE task_name VARCHAR2(30); BEGIN task_name : DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text SELECT * FROM your_table WHERE indexed_column :1, scope COMPREHENSIVE, time_limit 3600, task_name idx_usage_analysis); DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name); END; /6.2 索引使用趋势分析通过DBA_HIST_SQL_PLAN获取历史趋势SELECT TRUNC(timestamp) as day, COUNT(DISTINCT object_name) as used_indexes FROM dba_hist_sql_plan WHERE object_owner YOUR_SCHEMA AND object_type INDEX AND timestamp SYSDATE - 180 GROUP BY TRUNC(timestamp) ORDER BY day;6.3 索引存储分析结合存储使用情况决策SELECT segment_name as index_name, bytes/1024/1024 as size_mb, tablespace_name FROM dba_segments WHERE owner YOUR_SCHEMA AND segment_type LIKE INDEX% ORDER BY bytes DESC;