3
索引监控步骤.txt
免费下载
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
of 1
免费下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
文档被以下合辑收录
评论