Re: Tracking usage of index

What I have done:

• scrape the join of gv$sql and gv$sql_plan every 5 minutes
• save owner, index to a table for any new plans found in v$sql_plan

the longer it runs, the more you will know.

eg. that index that is important at the end of a quarter will only show up occasionally
and even then, you may want to drop it, and rebuild when necessary

After a year, any index that doesn't show up in a report is probably of questionable value.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Principal Consultant at Pythian
Oracle ACE Alumni
Pythian Blog http://www.pythian.com/blog/author/still/ Github: https://github.com/jkstill

On Thu, Nov 26, 2020 at 12:21 PM Lok P <loknath.73_at_gmail.com> 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?
>

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