Re: Tracking usage of index

Hi Lok,

You can turn on monitoring for index, something like "ALTER INDEX PK_EMP MONITORING USAGE". The usage will be in V$OBJECT_USAGE. Unfortunately, there is no owner in that table, so you need to log in as the index owner.

Regards

On 11/26/20 3:19 PM, Lok P wrote:
>
> 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?
>

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


--
http://www.freelists.org/webpage/oracle-l