Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training

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

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

spool off;


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


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

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


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:




Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.