Donald K. Burleson Oracle Tips

Locating and deleting unused indexes in Oracle9i

One of the most serious causes of poor DML performance is the existence of unused indexes.  All SQL inserts, updates and delete will run slower if they have to update a large number of indexes each time a row in a table is changed. 

Sadly, many Oracle professionals will allocate indexes whenever they see a column that is referenced in the WHERE clause of an SQL query.  While this approach makes SQL run fast, function-based Oracle indexes make it possible to over-allocate indexes on table columns.  This over-allocation of indexes can cripple the performance of loads on critical Oracle tables. 

Until Oracle9i, there was no way to identify those indexes that were not being used by SQL queries. This tip describes the Oracle9i method that allows the DBA to locate and delete un-used indexes.

The approach is quite simple.  Oracle9i has a tool that allows you to monitor index usage with an alter index command.  You can then query and find those indexes that are unused and drop them from the database.

Here is a script that will turn on monitoring of usage for all indexes in a system:

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,
   table_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               TABLE_NAME     MON USED

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

    CUSTOMER_LAST_NAME_IDX   CUSTOMER       YES NO  

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The DefinitiveReference” by Rampant TechPress. (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm