Which of my indexes are redundant and can be dropped ?

The Oracle (tm) Users' Co-Operative FAQ

Which of my indexes are redundant and can be dropped ?

The idea is to exclude indexes in active use from all the indexes in the system. And then check the leftovers to see if they have any use or value (this is the more manual part).

For Oracle versions prior to 9i, we can only se if an index has been in use in a given time frame. From a given number of indexes in a system, we can measure if a given index has been in use in the time frame we spend to analyze and measure. This then excludes the 'once a year' use of an index for reporting numbers from the general ledger annual report of something. And maybe we were better of, creating the index when called for, and drop it when the task has completed.

First goal is to find indexes that are actually in use, for purposes other than being maintained by dml's. I have chosen two ways to solve this. This first part is through the use of buffer cache (db_block_buffers) and a following part (still in work) is throughout the use of  library cache in shared pool (i.e.. v_$sql et.al.)

Buffer cache solution:

If an index is in use for active lookup, at least some blocks must show up in the buffer_cache, and remain there until aged out. If you don't use all tree buffer_caches, and have a newer database version, this is not very complicated to solve. Just takes some time. Create a keep or recycle pool of decent size, and target the suspect  indexes for this pool. Ie. alter index X storage(buffer_pool [keep|recycle]). After a while (hours or more), check if the targeted indexes is present in the pool at all or with a large percentage of dirty blocks. If that is the case, evaluate if the index can be dropped (seems likely).

If you use all tree buffer caches, or run an older version, here is a more generic solution. It goes: Select all indexes, total number of blocks in the buffer_caches, number of dirty blocks and time stamp per index and store this as a table. Create a view designed for update the table, and a stored procedure to do it. Run the stored procedure regularly to update the table. A small routine for this is supplied. The procedure updates the result table a time stamp and the increased number of blocks, if the total number or number of dirty blocks of a given index has increased. Run the procedure every say 5-15 minutes for a day more, depending on how fragile your buffer_caches is and on db usage pattern. Then check the table. Don't do any online index rebuilds, or anything  that is likely to disturb your metering tool. If this cant be avoided, clear metering values in the table and/or restart this procedure.
In case that the index hasn't had any blocks (recorded) in the buffer_caches, you can most likely drop it. It might be one of these 'once a period' indexes. Take appropriate action if that's the case. Or it might be that the underlying table is never accesses. Export table and all definitions, then rename or drop. Some evaluation is needed here.

If the mayor part of blocks of a given index are dirty, it is likely that is is only present in the buffer cache due to dml's.. Evaluate if the index can be dropped and record the index definition, drop or invalidate the index (if large).

Run the script (might have to change tablespaces). Run the procedure oci_index_cache_block_prc or supplied loop as fitting. When done, evaluate.

If you have lots of schema's and/or a large complex setup, use the commented code to evaluate schemes, one by one. The code here can have negative impact on busy systems.

/****************************************************************************************
  This program is free ware under terms of GNU's General Public License®
  and Open Source Foundation® as long as the copyright notice is not removed.
      Sysdate:         January 2002
      Username:        © 2002 Svend Jensen, Svend@OracleCare.Com

Rem
Rem     ==========================================================================
Rem     DISCLAIMER:
Rem     This script is provided for Oracle DBAs "AS IS". It is NOT supported
Rem     by author, Oracle World Wide Technical Support nor third parties.
Rem     The script has been tested and appears to work as intended.
Rem     NO responsibility taken for working nor use, no matter the circumstances.
Rem     You should always run new scripts on a test instance initially.
Rem     ===========================================================================
Rem     # Improvements and extensions are welcome.
******************************************************************************************/
    -- connect sys/<password> [as sysdba]
    -- creating base table for index usage investigation
    -- can be global temporary if you like. But can give problems with commit.
    -- Created: Svend Jensen 2002
    -- remove old stuff
    drop index oci_index_cache_block_udx
    ;
    drop view  oci_index_cache_block_vw
    ;
    drop table oci_index_cache_block_use
    ;
    -- now (re)create table, view, index and procedure
    create table oci_index_cache_block_use
    tablespace tools
    as
    select /*+ all_rows */ 
    object.owner, object.obj# index_obj#, object.index_name, object.table_obj#, object.table_name, 
    sum(decode(bhead.dirty,'Y',1,0)) dirty#datablock, 
    count(bhead.obj#) total#datablock, sysdate timestamp 
    from 
        (select  /*+ all_rows */ 
         u.name owner, o.obj#, o.name index_name, i.bo# table_obj#, o2.name table_name 
         --  i.bo# is table obj# for index row in ind$ 
         from   obj$ o, ind$ i, obj$ o2, user$ u
         where  o.obj#     = i.obj#
         and    o2.obj#    = i.bo#
         and    o.dataobj# = i.dataobj# 
         and    o.owner#   = u.user#
         --  and    o.owner#   = (select user# from user$ where name = upper('$user_name')) -- 
         --  uncommect if only user/schema owner to be checked, and fill in $user_name --   
        )      
        object,
        (select  objd as obj#, dirty
         from v_$bh  
         where  status != 'free'
        )       
        bhead
        where object.obj#  = bhead.obj#(+) 
        group by object.owner, object.obj#, object.index_name, object.table_obj#, object.table_name
        order by total#datablock desc
        ;

       -- for update of the base table with new or change entries found in buffer_cache
        create or replace view oci_index_cache_block_vw
       (owner, index_obj#, index_name, table_obj#,
        table_name, dirty#datablock,
        total#datablock, timestamp)
       as (
           select /*+ all_rows */ 
           object.owner, object.obj# index_obj#, object.index_name, object.table_obj#, object.table_name, 
           sum(decode(bhead.dirty,'Y',1,0)) dirty#datablock, 
           count(bhead.obj#) total#datablock, sysdate timestamp 
           from 
                (select  /*+ all_rows */ 
                 u.name owner, o.obj#, o.name index_name, i.bo# table_obj#, o2.name table_name 
                 --  i.bo# is table obj# for index row in ind$ 
                 from   obj$ o, ind$ i, obj$ o2, user$ u
                 where  o.obj#     = i.obj#
                 and    o2.obj#    = i.bo#
                 and    o.dataobj# = i.dataobj# 
                 and    o.owner#   = u.user#
                 --  and    o.owner#   = (select user# from user$ where name = upper('$user_name')) -- 
                 --  uncommect if only user/schema owner to be checked, and fill in $user_name --   
                )      
                object,
                (select  objd as obj#, dirty
                 from v_$bh  
                 where  status != 'free'
                )       
                bhead
                where object.obj#  = bhead.obj# -- (+): select only hits in buffer_cache 
                group by object.owner, object.obj#, object.index_name, 
                object.table_obj#, object.table_name
                ) with check option
       ;

       -- create index on table oci_index_cache_block_use for updating
       create unique index oci_index_cache_block_udx
       on oci_index_cache_block_use
       (owner, index_obj#, index_name, table_obj#, table_name)
       compute statistics 
       tablespace INDX  
       ;


       -- create update procedure
      create or replace procedure oci_index_cache_block_prc
      as
      /* variables */
      v_owner              varchar2(30) ;
      v_index_obj#         number       ;
      v_index_name         varchar2(30) ;
      v_table_obj#         number       ;
      v_table_name         varchar2(30) ;
      v_dirty#datablock    number       ;
      v_total#datablock    number       ;
      v_timestamp          date         ;
      /* cursors */
      cursor cur_oci_index is select * from OCI_INDEX_CACHE_BLOCK_VW ;
      BEGIN 
      open   cur_oci_index ;
      fetch  cur_oci_index into  v_owner, v_index_obj#, v_index_name, v_table_obj#,
                                 v_table_name, v_dirty#datablock,
                                 v_total#datablock, v_timestamp ;
     while cur_oci_index%FOUND
     LOOP
         update OCI_INDEX_CACHE_BLOCK_USE 
         set  total#datablock = v_total#datablock, 
              timestamp        = v_timestamp
         where owner           = v_owner
         and   index_obj#      = v_index_obj#
         and   index_name      = v_index_name 
         and   table_obj#      = v_table_obj#
         and   table_obj#      = v_table_obj#  
         and   total#datablock < v_total#datablock 
         ;

        update OCI_INDEX_CACHE_BLOCK_USE 
        set  dirty#datablock  = v_dirty#datablock, 
        timestamp        = v_timestamp
        where owner           = v_owner
        and   index_obj#      = v_index_obj#
        and   index_name      = v_index_name 
        and   table_obj#      = v_table_obj#
        and   table_obj#      = v_table_obj#  
        and   dirty#datablock < v_dirty#datablock
        ;                  
         
        fetch  cur_oci_index into  v_owner, v_index_obj#, v_index_name, v_table_obj#,
                                   v_table_name, v_dirty#datablock,
                                   v_total#datablock, v_timestamp ;
        END LOOP ;
        commit ;
        close cur_oci_index ;
   
     EXCEPTION
          WHEN OTHERS THEN
           IF cur_oci_index%ISOPEN THEN
                   close cur_oci_index ;
              END IF ;
              dbms_output.put_line ('Others execption in oci_index_cache_block_prc '||SQLERRM) ;
              RAISE ;

     END ;
     /
/**************** a little update loop **************************
    begin
    for j in 1..30 loop
   oci_index_cache_block_prc ;
   dbms_lock.sleep(300) ; -- sleep 5 minutes
   end loop ;
  end ;
  /
*********************************************************/

End of story - have fun.

Further Reading: Oracle concepts manual, http://www.oracle.com http://technet.oracle.com , http://metalink.oracle.com

This question is also addressed by the following documents: