oracle --索引监控

1、查询表上索引个数超过5个的索引
select owner,table_name, count(*) cnt
from dba_indexes
where owner ='META'
group by owner,table_name
having count(*) >= 5;
order by cnt desc
2、将这些索引监控起来
select 'alter index '||index_name||' monitoring usage;' from
(select table_name,index_name from dba_indexes where owner ='META')A,
(select owner,table_name, count(*) cnt from dba_indexes where owner ='META' group by owner,table_name having count(*) >= 5 )B
where A.table_name=B.table_name;
3、监控一个月查看索引是否使用
SELECT *
FROM v$object_usage t
WHERE t.MONITORING='YES' and t.used = 'NO';--查看从未使用的索引
4、查询索引大小
select sum(BYTES/1024/1024) M from dba_segments where segment_name in
(
select index_name from v$object_usage where MONITORING='YES' AND USED='NO' and index_name
IN (select index_name from user_indexes where UNIQUENESS='NONUNIQUE')
);
5、查询不使用索引，是否为主键，唯一键，外键
select * from v$object_usage A where A.MONITORING='YES' AND A.USED='NO'
and upper(index_name) not in (
select upper(CONSTRAINT_NAME) from user_constraints c where c.constraint_type in( 'P'/*主键*/ ,'U'/*唯一*/,'R'/*外键*/)
and c.table_name=A.table_name
) and exists
(
select 1 from user_indexes c where c.index_name=A.index_name and C.UNIQUENESS='NONUNIQUE'
)
6、未使用的索引，将其删除
若在4中存在！！！切记不要删除
select 'drop index '||INDEX_NAME||';' from v$object_usage A where A.MONITORING='YES' AND A.USED='NO'
and upper(index_name) not in (
select upper(CONSTRAINT_NAME) from user_constraints c where c.constraint_type in( 'P'/*主键*/ ,'U'/*唯一*/,'R'/*外键*/)
and c.table_name=A.table_name
) and exists
(
select 1 from user_indexes c where c.index_name=A.index_name and C.UNIQUENESS='NONUNIQUE'
)
7、取消索引监控
alter index <INDEX_NAME> nomonitoring usage;
select ' alter index '||INDEX_NAME||' nomonitoring usage;' from v$object_usage where MONITORING='YES';

**********************************************第二轮监控*******************************************
1、查看监控的索引
select * from v$object_usage where MONITORING='YES'

2、接下来的步骤用3,4,5,6,7