We have a database which maxed its CPU capacity. and I did some SQL
tuning /index creation. I happened to think some index should be
useless and I enabled index monitoring for some index and they do
showed used='NO' in v$object_usage.
Later I enabled index monitoring in all the indexes for this user, and I see:
SQL> select used,count(*) from v$object_usage group by used;
and these indexes used 36G space:
1 select uniqueness, sum(bytes) from user_segments a,
user_indexes b where segment_name in
2 (select index_name from v$object_usage where used='NO')
3 and a.segment_name=b.index_name
4* group by uniqueness
I plan to drop those indexes. But I am not sure whether there is
bugs/issues with the v$object_usage that it does not report some used
index, or under some circumstance, even SQL don't use the index, we
have to keep these indexes.
One possible is unique index. Unique index is not used to speedup SQL,
but to enforce business logic.
The other is for the FK related index. But we are running oracle
126.96.36.199 and I think it is no longer an issue.
Can someone share your experience/opinion on this?