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

Home
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


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

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

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

 

 

 
 
 

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.