Re: Tracking usage of index

From: Jared Still <>
Date: Thu, 10 Dec 2020 16:40:49 -0800
Message-ID: <>

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 Github:

On Thu, Nov 26, 2020 at 12:21 PM Lok P <> wrote:

> We have just moved from Oracle version 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 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?

Received on Fri Dec 11 2020 - 01:40:49 CET

Original text of this message