index 多了也不是好事,会带来一下insert 及update 的开销
当前管理的系统发现
select segment_name,sum(blocks)*8/1024/1024
from user_segments where segment_name like 'IND_TOH_9102%'
group by segment_name;;
from user_segments where segment_name like 'IND_TOH_9102%'
group by segment_name;;
IND_TOH_9102_IDX1 38.5968627929688
IND_TOH_9102_IDX2 38.8302612304688
IND_TOH_9102 110.6572265625
IND_TOH_9102_IDX2 38.8302612304688
IND_TOH_9102 110.6572265625
两个index 各占38GB 加起来快过了表的尺寸。
查看两个index
create unique index IND_TOH_9102_IDX1 on IND_TOH_9102 (STARTDAY, STARTTIME, MOENTITYID);
create index IND_TOH_9102_IDX2 on IND_TOH_9102 (STARTDAY, MOENTITYID, STARTTIME);
create index IND_TOH_9102_IDX2 on IND_TOH_9102 (STARTDAY, MOENTITYID, STARTTIME);
发现只是前导列有所不同,询问研发, 也不说不清楚。
开启 index 的监控
alter index ind_toh_9102_idx1 monitoring usage;
alter index ind_toh_9102_idx2 monitoring usage;
alter index ind_toh_9102_idx2 monitoring usage;
过了1月
查询监控index 的使用情况
select * from v$object_usage;
1 IND_TOH_10142_IDX1 IND_TOH_10142 NO YES 04/05/2013 17:00:47 04/05/2013 17:49:08
2 IND_TOH_10142_IDX2 IND_TOH_10142 NO NO 04/05/2013 17:06:23 04/05/2013 17:49:16
3 IND_TOH_10395_IDX2 IND_TOH_10395 YES NO 04/05/2013 17:49:35
4 IND_TOH_10395_IDX1 IND_TOH_10395 YES YES 04/05/2013 17:49:46
5 IND_TOH_9102_IDX1 IND_TOH_9102 YES YES 04/11/2013 14:00:22
6 IND_TOH_9102_IDX2 IND_TOH_9102 YES NO 04/11/2013 14:00:42
2 IND_TOH_10142_IDX2 IND_TOH_10142 NO NO 04/05/2013 17:06:23 04/05/2013 17:49:16
3 IND_TOH_10395_IDX2 IND_TOH_10395 YES NO 04/05/2013 17:49:35
4 IND_TOH_10395_IDX1 IND_TOH_10395 YES YES 04/05/2013 17:49:46
5 IND_TOH_9102_IDX1 IND_TOH_9102 YES YES 04/11/2013 14:00:22
6 IND_TOH_9102_IDX2 IND_TOH_9102 YES NO 04/11/2013 14:00:42
发现2 号index 里从来没有被使用过。
drop 之
同时
关闭 其它index 的监控
alter index ind_toh_9102_idx1 nomonitoring usage;
还有来自maclean liu 的script 不用监控,但不能保证保证100%准确
select p.object_name c1, p.operation c2, p.options c3,count(1) c4
from dba_hist_sql_plan p, dba_hist_sqlstat s
where p.object_owner <> 'SYS'
and p.operation like '%INDEX%'
and p.sql_id = s.sql_id
group by p.object_name,p.operation, p.options order by 1,2,3;
from dba_hist_sql_plan p, dba_hist_sqlstat s
where p.object_owner <> 'SYS'
and p.operation like '%INDEX%'
and p.sql_id = s.sql_id
group by p.object_name,p.operation, p.options order by 1,2,3;