How to Find Unused Indexes

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:

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:

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

To stop monitoring on all indexes:

You can run the following query to find unused indexes:

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

Please share
  •  
  •  
  •  
  •  
  •  
  •  

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.

12 Comments

  1. Mike Pazda

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

  2. rst

    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

      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 🙂

    •  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.
       

  3. mehmet sulun

    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 ?

     

  4. Olga

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

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

  5. Jeff K

    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

      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.

  6. Hemanth

    Hi Gokhan Atil,

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

    Thanks,

    Hemanth

  7. Dusan

    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

Your email address will not be published. Required fields are marked *

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