开发人员提出:需要对之前的索引监控 然后找出没用的索引 找时间删除 从而减少未用索引占用空间 提高DML效率
此文只是 监控索引 对监控结果 需要进行统计分析 然后可以考虑先unusable 索引 观察一段时间 再drop index
索引监控Procedure
创建日志表
create table tt (tname varchar2(32),vsql varchar2(500),vdate date);
创建监控信息结果表
create table TMP_OBJECT_USAGE as
select trunc(sysdate) vdate,t.*from V$OBJECT_USAGE t where 0=1;
创建存储过程
create or replace procedure P_IDX_MONITORING (V_SCHEMA varchar2,IS_ON number)
as
-- Create by yujx 2014/12/25
/* V_SCHEMA 需要监控索引的schema
IS_ON 1:开启索引监控 2:关闭索引监控
-- LOG表
drop table tt purge;
create table tt (tname varchar2(32),vsql varchar2(500),vdate date);
-- 索引监控结果表
create table TMP_OBJECT_USAGE as
select trunc(sysdate) vdate,t.*from V$OBJECT_USAGE t where 0=1;
*/
V_SQL varchar2(1000);
v_error clob;
Begin
delete tt;
-- 监控索引
IF IS_ON=1 THEN
for i in (SELECT table_name,'ALTER INDEX ' || owner || '.' || index_name ||
' MONITORING USAGE' vtext
FROM dba_indexes where index_type<>'LOB' and owner in (V_SCHEMA)) loop
v_sql :=i.vtext;
execute immediate v_sql;
insert into tt values(i.table_name,v_sql,sysdate);
End loop;
END IF;
/* 查看索引使用情况
select * from V$OBJECT_USAGE;*/
--关闭监控
IF IS_ON=2 THEN
for i in (SELECT table_name,
'ALTER INDEX ' || owner || '.' || index_name ||
' NOMONITORING USAGE' vtext
FROM dba_indexes where index_type<>'LOB' and owner in (V_SCHEMA)) loop
v_sql :=i.vtext;
execute immediate v_sql;
insert into tt values(i.table_name,v_sql,sysdate);
End loop;
-- 记录本次监控信息到结果表
insert into TMP_OBJECT_USAGE select trunc(sysdate),t.* from V$OBJECT_USAGE t;
END IF;
commit;
EXCEPTION
WHEN OTHERS THEN
v_error := SQLERRM;
.....此处根据实际情况处理异常(发邮件、插入异常日志表等)
end;
使用说明
P_IDX_MONITORING(‘需要监控的schema’,’1或者2’);
--1表示开启监控 2表示关闭监控
如
--对TEST开启索引监控功能
BEGIN
P_IDX_MONITORING('TEST',1);
END;
--关闭索引监控功能
BEGIN
P_IDX_MONITORING('TEST',2);
END;
日志查看
索引监控JOB
开启index监控
--每天晚上22点开启索引监控
variable job number;
begin
sys.dbms_job.submit(job => :job,
what => ' P_IDX_MONITORING(''TEST'',1);',
next_date => sysdate,
interval => 'TRUNC(sysdate) + 1 +22/24');
commit;
end;
/
关闭index监控
--每天早上8点关闭索引监控
variable job number;
begin
sys.dbms_job.submit(job => :job,
what => ' P_IDX_MONITORING(''TEST'',2);',
next_date => sysdate,
interval => 'TRUNC(sysdate) + 1 +8/24');
commit;
end;
/
查看监控结果
select * from TMP_OBJECT_USAGE;
--可以查询到每天监控时间段内的使用情况
![](http://img.blog.itpub.net/blog/attachment/201412/26/27000195_1419565181p28z.png?x-oss-process=style/bb)
--USED为yes表示监控期间 对应的索引被使用
基于上面的监控结果 可以进行统计分析 从而评估索引有没有被使用
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27000195/viewspace-1380770/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27000195/viewspace-1380770/