How to Find Unused Indexes – Gokhan Atil’s Blog

You can use Monitoring feature of Oracle to find which indexes are unused. You can run the following query to enable monitoring on an index:

ALTER INDEX index_name MONITORING USAGE;

After you enable indexing, you should wait for a while (for example 1-2 days if you think that this index should be used daily), and then you can query v$object_view to see if index is used:

SELECT * FROM v$object_usage;

INDEX_NAME,TABLE_NAME,MONITORING,USED,START_MONITORING,END_MONITORING
———————————————————————————————————————-
EMP_EMAIL_UK,EMPLOYEES,YES,YES,08/28/2010 22:06:23,08/28/2010 22:24:10

SELECT * FROM v$object_usage;

INDEX_NAME,TABLE_NAME,MONITORING,USED,START_MONITORING,END_MONITORING

———————————————————————————————————————-

EMP_EMAIL_UK,EMPLOYEES,YES,YES,08/28/2010 22:06:23,08/28/2010 22:24:10

You can run the following query to disable monitoring of an index:

ALTER INDEX index_name NOMONITORING USAGE;

Disabling monitoring an index, does not change the “used” value on v$object_usage; If you query that view, you should still use the index as used. Because the “used” property shows if index is used between start_monitoring and end_monitoring period.

v$object_view shows the index monitoring information of only the current schema. As a DBA, if you want to check all indexes, you should create a new view:

CREATE VIEW dba_object_usage (owner,
index_name, table_name, monitoring, used, start_monitoring, end_monitoring )
AS
SELECT do.owner, io.NAME, t.NAME,
DECODE (BITAND (i.flags, 65536), 0, 'NO', 'YES'),
DECODE (BITAND (ou.flags, 1), 0, 'NO', 'YES'), ou.start_monitoring,
ou.end_monitoring
FROM SYS.obj$ io,
SYS.obj$ t,
SYS.ind$ i,
SYS.object_usage ou,
dba_objects do
WHERE i.obj# = ou.obj#
AND io.obj# = ou.obj#
AND t.obj# = i.bo#
AND ou.obj# = do.object_id;

CREATE VIEW dba_object_usage (owner,

index_name, table_name, monitoring, used, start_monitoring, end_monitoring )

SELECT do.owner, io.NAME, t.NAME,

DECODE (BITAND (i.flags, 65536), 0, 'NO', 'YES'),

DECODE (BITAND (ou.flags, 1), 0, 'NO', 'YES'), ou.start_monitoring,

ou.end_monitoring

FROM SYS.obj$ io,

SYS.obj$ t,

SYS.ind$ i,

SYS.object_usage ou,

dba_objects do

WHERE i.obj# = ou.obj#

AND io.obj# = ou.obj#

AND t.obj# = i.bo#

AND ou.obj# = do.object_id;

To enable monitoring on all indexes, run the following script:

SET heading off
SET echo off
SET pages 10000
SPOOL start_monitoring.sql
SELECT 'alter index ' || owner || '.' || index_name || ' monitoring usage;'
FROM dba_indexes
WHERE owner NOT IN ('SYS', 'SYSTEM');
SPOOL off
@start_monitoring.sql

SET heading off

SET echo off

SET pages 10000

SPOOL start_monitoring.sql

SELECT 'alter index ' || owner || '.' || index_name || ' monitoring usage;'

FROM dba_indexes

WHERE owner NOT IN ('SYS', 'SYSTEM');

SPOOL off

@start_monitoring.sql

To stop monitoring on all indexes:

SET heading off
SET echo off
SET pages 10000
SPOOL stop_monitoring.sql
SELECT 'alter index ' || owner || '.' || index_name || ' nomonitoring usage;'
FROM dba_indexes
WHERE owner NOT IN ('SYS', 'SYSTEM');
SPOOL off
@stop_monitoring.sql

SET heading off

SET echo off

SET pages 10000

SPOOL stop_monitoring.sql

SELECT 'alter index ' || owner || '.' || index_name || ' nomonitoring usage;'

FROM dba_indexes

WHERE owner NOT IN ('SYS', 'SYSTEM');

SPOOL off

@stop_monitoring.sql

You can run the following query to find unused indexes:

SELECT * FROM dba_object_usage WHERE used = 'NO';

1	SELECT * FROM dba_object_usage WHERE used = 'NO';

Last thing about monitoring indexes: If you analyze an index, it will be marked as “used”.

Please share

12 Comments

Mike Pazda

April 9, 2013 Reply

I wouldnt have thought it was good practice to create your own views prefixed with DBA_
Hemant K Chitale

July 24, 2013 Reply

12c introduces the DBA_OBJECT_USAGE view. V$OBJECT_USAGE is replaced by USER_OBJECT_USAGE.

Hemant K Chitale
- Gokhan Atil
  
  July 24, 2013
  
  Hemant, thanks!
rst

June 2, 2015 Reply

Hi Gokham ,

I have small confusion about indexes.

A table contains 60 million records. It has constriants and indexes.

At that time of export , i did not disable constraints and indexes.

After import V$object_usage shows ‘indexes are NOT monitoring’.

What’s your advise on this scenario ,

A schema contains 1000 indexes, i have to use 1000 times following

SQL statement .

alter index <index_name> monitoring usage;

Please provide the solution.

Thanks
- Gokhan Atil
  
  June 2, 2015
  
  Hi Rst,
  
  You may spool your query output and run the file. For example, run the following in sqlplus:
  
  spool tempfile.sql
  select ‘alter index ‘ || index_name || ‘ monitoring usage;’ cmd from user_indexes;
  spool off
  @tempfile.sql
  
  That will do the trick 🙂
- Michael
  
  September 11, 2015
  
  You can use this smal anonymous block.
  DECLARE
  CURSOR c_ind IS
  SELECT index_name
  FROM USER_INDEXES
  WHERE INDEX_TYPE ‘LOB’
  AND INDEX_NAME NOT IN (SELECT DISTINCT INDEX_NAME from V$OBJECT_USAGE);
  
  TYPE t_ind_tab IS TABLE OF c_ind%ROWTYPE;
  t_index_tab t_ind_tab := t_ind_tab();
  
  BEGIN
  OPEN c_ind;
  FETCH c_ind BULK COLLECT INTO t_index_tab;
  close c_ind;
  
  FOR i IN 1 .. t_index_tab.COUNT LOOP
  execute immediate ‘alter index ‘||t_index_tab(i).index_name||’ monitoring usage’;
  END LOOP;
  
  END;
  /
  
  Run it again if there is comming back a message with busy objects.
mehmet sulun

August 12, 2015 Reply

merhaba Gökhan Hocam,

bu konuda bir şey sormak istiyorum tüm indexleri yeterli bir süre monitor ettiğimde foreignkey ler üzerindeki indexlerin kullanılmadıgını görüyorum oracle’da bunun hakkında fikriniz varmı acaba ?

foreignkey ler üzerindeki indexlerin usage yakalanmaması gibi bir durum söz konusu olabilirmi ?
Olga

February 26, 2016 Reply

Thank you for your article! It’s very useful!

But I’m wondering, is it possible to find out what queries use indexes?
Jeff K

March 3, 2016 Reply

Mr. Atil,

Is there a way that you know of to monitor usage of index partitions and subpartitions? Or does the “global” index include those? Thanks!
- Gokhan Atil
  
  March 4, 2016
  
  Jeff, as I know, you can enable monitoring for a partitioned index at global level, and even only one subpartition is used, you’ll see that your partitioned index is marked as “used”. I didn’t examine the situation in Oracle 12c yet, so I don’t know if this behaviour is changed or monitoring capabilities are improved.
Hemanth

March 14, 2016 Reply

Hi Gokhan Atil,

How to find out INDEX usage for a particular tables in last 30 days.

Thanks,

Hemanth
Dusan

August 24, 2017 Reply

This is ho to find unused indexes based on awr snapshot.

col c1 heading ‘Begin|Interval|time’ format a20

col c2 heading ‘Search Columns’ format 999

col c3 heading ‘Invocation|Count’ format 99,999,999

break on c1 skip 2

accept idxname char prompt ‘Enter Index Name: ‘

ttitle ‘Invocation Counts for index|&idxname’

select

to_char(sn.begin_interval_time,’yy-mm-dd hh24′) c1,

p.search_columns c2,

count(*) c3

from

dba_hist_snapshot sn,

dba_hist_sql_plan p,

dba_hist_sqlstat st

where

st.sql_id = p.sql_id

and

sn.snap_id = st.snap_id

and

p.object_name = ‘&idxname’

group by

begin_interval_time,search_columns;

Leave Comment Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.