Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, mrunmayi.

Asked: January 16, 2017 - 10:41 am UTC

Last updated: April 28, 2022 - 4:22 am UTC

Version: 12 c

Viewed 10K+ times! This question is

You Asked

Hello Tom,

i was looking for options available in oracle 12c by which i can find out Unused indexes in database. I have used v$object_usage. but it only gives me index is used? it does not display how many times it has been used. also, if i would like to know is there any methodology available which will help me to find out if created index is useful (i know its vague stmt but just in case if any process is available).
My requirement is, i have to remove unused and not required indexes.

thanks in advance.

and Chris said...

Up to 12.1 you can enable index monitoring. This is useful but only takes you so far as you say. And it misses some important cases, such as if Oracle Database used an FK index when you delete from the parent with an "on delete cascade" FK.

This is improved in 12.2 to show more details in dba_index_usage. But it still misses some cases, such as when the optimizer uses multi-column indexes to determine plan cardinalities. Connor and I discuss this at:

https://www.youtube.com/watch?v=oib5smLnA-g

And you can read more about it at:

http://blog.dbi-services.com/12cr2-new-index-usage-tracking/

Ultimately the only way to be certain you're not using an index is a complete regression test of your whole application.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks a lot!!

mrunmayi kusurkar, January 16, 2017 - 12:17 pm UTC

Thanks a lot for quick response.. its truly useful..

Question on DBA_INDEX_USAGE and gather stats

Aníbal, November 04, 2021 - 3:30 pm UTC

Hi guys, is there a way to distinguish an index being used by a gathering stats procedure than one being used by a sql statement in DBA_INDEX_USAGE?
Connor McDonald
November 09, 2021 - 3:40 am UTC

Not really, but perhaps you could do an approximation, ie

You could use stats history to determine how many times you have gathered stat on this index , and gather stats scans the entire index (assuming you are using the default AUTO method).

If the number of entries in the index is (say) 600, then you know that if you have gathered stats 3 times, then you will have 3 "erroneous" counts in the BUCKET_101_1000_ACCESS_COUNT column, and 3x600 excess in the BUCKET_101_1000_ROWS_RETURNED column.

You could remove these to get a slightly better estimate of index usage.

BUCKET_1000_PLUS_ACCESS_COUNT

Mansi, April 25, 2022 - 11:05 am UTC

I hav been keeping eye on DBA_INDEX_USAGE for few indexes and it was last used in somewhere in Dec-21. Now that when I see its on 08-APR-22.
I dont think its getting used anywhere, but gather stats happend near 08-APR-22.
I came to this post if is there any way to confirm that. You say there should be only 1 count for 1 gather stats, I can see the count is 200 and 52 in 2 indexes I have been monitoring. Is it possible in gahter stat?
P.S. table has 609861237 rows returned.
Chris Saxon
April 25, 2022 - 2:09 pm UTC

Where exactly are you seeing these values? What were the values before stats were gathered?

BUCKET_101_1000_ACCESS_COUNT

A reader, April 26, 2022 - 7:45 am UTC

I am not sure about the previous values, I only checked that it was not used since long time. checked the last _USED_date.
and now BUCKET_101_1000_ACCESS_COUNT has this counts and
last _USED_date is 8th apr. Also total count (*) in BUCKET_1000_PLUS_ROWS_RETURNED.
Connor McDonald
April 28, 2022 - 4:22 am UTC

A way of cross-checking would be to check v$sql_plan and the equivcalent DBA_HIST_... tables for sql statements that reference this index.

If you can't find any, then perhaps explore setting it to invisible and monitoring for any regressions.

BUCKET_1000_PLUS_ACCESS_COUNT

Mansi, April 26, 2022 - 7:47 am UTC

I am not sure about the previous values, I only checked that it was not used since long time. checked the last _USED_date.
and now BUCKET_1000_PLUS_ACCESS_COUNT has this counts and
last _USED_date is 8th apr. Also total count (*) in BUCKET_1000_PLUS_ROWS_RETURNED.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.