We have just moved from Oracle version 11.2.0.4 to 19.3. And also we were
on HP and now we moved to Exdata-X5. And the team is asking to verify if
some of the indexes are actually not in use as we are in Exadata and thus
we can drop them afterwards. So was trying to understand the reliable way
to achieve the list of unused indexes?
As per my understanding 11.2.0.4 was having option for setting the
monitoring On for the specific indexes, to see its usage from
dba_object_usage, but that was just a "YES" or "NO" flag which was not of
much help(as amount of usage is also not captured there) and also stats
gathering on the index was making the usage flag to YES which is wrong,
also indexes on foreign key were not getting picked up as USED in cases.
But i see starting from version 12.2+ , Oracle is by-default capturing all
the information in a new view DBA_INDEX_USAGE, so wanted to understand from
experts if this is safe and reliable way to collect index usage information
or if we have anything new on 19C more reliable for finding index usage?
