数据库 表(oracle查询某数据库用户下哪些表的索引没有被使用到？)

1. 开启所有索引的监控

-- 为指定用户下的所有索引开启监控BEGINFOR idx IN (SELECT owner, index_name FROM dba_indexes WHERE owner = 'YOUR_SCHEMA')LOOPEXECUTE IMMEDIATE 'ALTER INDEX ' || idx.owner || '.' || idx.index_name || ' MONITORING USAGE';END LOOP;END;/

2. 运行一段时间后检查使用情况

-- 查看监控结果（需要DBA权限）SELECTu.owner,u.index_name,i.table_name,u.monitoring,u.used,u.start_monitoring,u.end_monitoring,i.index_type,i.uniqueness,i.statusFROM v$object_usage uJOIN dba_indexes i ON u.index_name = i.index_name AND u.owner = i.ownerWHERE u.owner = 'YOUR_SCHEMA'AND u.used = 'NO' -- 未使用的索引ORDER BY i.table_name, u.index_name;

3. 关闭监控

-- 监控完成后关闭监控BEGINFOR idx IN (SELECT owner, index_name FROM dba_indexes WHERE owner = 'YOUR_SCHEMA')LOOPEXECUTE IMMEDIATE 'ALTER INDEX ' || idx.owner || '.' || idx.index_name || ' NOMONITORING USAGE';END LOOP;END;/

4.查询AWR中索引使用统计（需要AWR许可）--推荐使用，可以获取哪些索引使用频繁最高，哪些索引使用频繁最低，适用于生产慢SQL优化

-- 查看过去N天内索引在AWR中的使用情况SELECTd.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_seenFROM dba_hist_sql_plan dJOIN dba_indexes i ON d.object_owner = i.owner AND d.object_name = i.index_nameJOIN dba_tables t ON i.table_owner = t.owner AND i.table_name = t.table_nameWHERE 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_nameORDER BY sql_count ASC; -- 使用次数少的排在前面

5.从未在AWR中出现的索引（可能从未使用）--推荐使用，用于优化生产无效索引

-- 找出从未在AWR执行计划中出现的索引SELECTi.owner,i.index_name,i.table_name,i.index_type,i.uniqueness,i.status,i.num_rows,i.leaf_blocksFROM dba_indexes iLEFT JOIN (SELECT DISTINCT object_owner, object_nameFROM dba_hist_sql_planWHERE object_type = 'INDEX'AND timestamp > SYSDATE - 90 -- 最近90天) p ON i.owner = p.object_owner AND i.index_name = p.object_nameWHERE i.owner = 'YOUR_SCHEMA'AND p.object_name IS NULL -- 从未出现在AWR中AND i.index_type NOT IN ('LOB', 'DOMAIN') -- 排除特殊类型索引ORDER BY i.table_name, i.index_name;