Oracle - Find unused Indexes Oracle Tips by Burleson Consulting

Also see my notes on detecting duplicate Oracle index columns and how to detect duplicate table rows.  There are 3 ways to write duplicate row detection queries.

Also, see my important notes on finding infrequently used indexes and detecting duplicate index columns.

Finding unused indexes in Oracle 10g and beyond

In Oracle10g we can easily see what indexes are used, when they are used and the context where they are used. Here is a STATSPACK query to find un-used indexes. 

You can also download an AWR script to detect unused indexes from the history tables:

Note in the script above the clause to ensure that the index is not used to enforce a primary key or foreign key relationship. 

Even though an index may appear to be used by SQL, it may be heavily used to enforce primary key and foreign key constraints.

Sun Jan 10                                                page    1

                  Unused Indexes by Time Period

Index Owner                    Index Name

------------------------------ ------------------------------

ROBOHEAD                       CIX_ACCTERM_TID

ROBOHEAD                       CIX_ACTEXP_BETID

ROBOHEAD                       CIX_ACTEXP_CID

ROBOHEAD                       CIX_ACTEXP_ETID

ROBOHEAD                       CIX_ACTEXP_PID

ROBOHEAD                       CIX_ACTLOG_DID_DTYP_LFID

ROBOHEAD                       CIX_APPROVAL_STATUS

ROBOHEAD                       CIX_APPROVER_USERID

ROBOHEAD                       CIX_ATTREF_SCHT

ROBOHEAD                       CIX_AUDIT_NOTESID

When dropping un-used indexes, also watch out for any indexes that may be used internally to enforce primary key foreign key relationships. 

If you have the licenses you can use STATSPACK and AWR to plot index usage.  Here is another script for tracking unused indexes and showing the invocation count of all indexes.  Best of all, this script shows the columns referenced for multi-column indexes:

This will produce an output like this, showing a summary count of the index specified during the snapshot interval. This can be compared to the number of times that a table was invoked from SQL.  Here is a sample of the output from this script:

 Here is a simple AWR query to plot index usage:

col c1 heading ‘Object|Name’       format a30
col c2 heading ‘Operation’         format a15
col c3 heading ‘Option’            format a15
col c4 heading ‘Index|Usage|Count’ format 999,999

break on c1 skip 2
break on c2 skip 2

select
   p.object_name c1,
   p.operation   c2,
   p.options     c3,
   count(1)      c4
from
   dba_hist_sql_plan p,
   dba_hist_sqlstat s
where
   p.object_owner <> 'SYS'
and
   p.operation like ‘%INDEX%’
and
   p.sql_id = s.sql_id
group by
   p.object_name,
   p.operation,
   p.options
order by
   1,2,3;

You can still locate unused indexes in Oracle 9i and Oracle 8i, but the mechanism for finding the unused indexes is not as easy.

Finding unused indexes on Oracle9i

One of the great features of Oracle9i is the ability to easily locate and remove unused indexes.  When an index is not used by SQL queries with the cost-based optimizer, the unused indexes waste space and cause INSERT statements to run slower.

Here is a simple SQL*Plus script to track all index usage in all Oracle schemas:

set pages 999;
set heading off;
spool run_monitor.sql

select
   'alter index '||owner||'.'||index_name||' monitoring usage;'
from
   dba_indexes
where
   owner not in ('SYS','SYSTEM','PERFSTAT')
;

spool off;

@run_monitor

Next, we wait until a significant amount of SQL has executed on our database, and then query the new v$object_usage view:

select
   index_name
   mon,
   used
from
   v$object_usage;

Here we see that v$object_usage has a single column called used, which will be set to YES or NO.  Sadly, this will not tell you how many times the index has been used, but this tool is useful for investigating unused indexes.

INDEX_NAME              MON USED

---------------         --- ----

CUSTOMER_LAST_NAME_IDX  YES  NO  
 

Schema owners and finding unused indexes

The problem is that the v$object_usage view uses the username logged into database when the alter  index index_name monitoring usage; command  is issued.

The solution is to bypass the v$object_usage view and query the underlying sys.object_usage fixed table.  This solution is from Oracle MetaLink:

select 
   io.name, t.name,
   decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
   decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
   u.start_monitoring,
   ou.end_monitoring 
from   
   sys.obj$ io,
   sys.obj$ t,
   sys.ind$ i,
   sys.object_usage ou 
where
   io.owner# = userenv('SCHEMAID') 
and 
   i.obj# = ou.obj# 
and 
   io.obj# = ou.obj# 
and 
   t.obj# = i.bo#;

This is another possible solution published by Oracle guru Rich Jesse:

select 

   u.name "owner",

   io.name "index_name",

   t.name "table_name",

   decode(bitand(i.flags, 65536), 0, 'no', 'yes') "monitoring",

   decode(bitand(nvl(ou.flags,0), 1), 0, 'no', 'yes') "used",

   ou.start_monitoring "start_monitoring",

   ou.end_monitoring "end_monitoring"

from 

   sys.obj$ io, 

   sys.obj$ t, 

   sys.ind$ i, 

   sys.object_usage ou, sys.user$ = u

where 

   t.obj# =3d i.bo#

and 

   io.owner# =3d u.user#

and 

   io.obj# =3d i.obj#

and 

   u.name not in ('sys','system')

and 

   i.obj# =3d ou.obj#(+);

Finding unused indexes in Oracle8i

This same technique for finding unused indexes can be done in Oracle8i, but it involves running complex scripts to interrogate the Oracle 8i library cache and parse for index names.  Col. John Garmany has developed a complete method to detect and remove un-used indexes in Oracle8 and Oracle8i databases.