Donald K. Burleson Oracle Tips

Oracle9i monitoring of indexes

Prior to Oracle9i, the only way for an Oracle DBA to monitor the usage of indexes was to derive the execution plan for all SQL inside their library cache and manually note all indexes that are used.

Recent studies have found that an Oracle database never uses more than a quarter of all indexes available or doesn't use them in the way for which they were intended.  Un-used indexes waste space and also slow-down all DML, especially UPDATE and INSERT statements.

Oracle9i provides an easy way to turn-on and turn-off tracking index usage with the “monitoring usage” clause:

alter index cust_name_idx monitoring usage;

alter index cust_name_idx nomonitoring usage;

This command is tied to the v$object_usage view and the USED column that tells if the index has been accessed.  Now, you would expect the USED column to be a numeric values that tells you the number of times that the index was used, but sadly, it is only a “YES” or “NO” column.

Regardless, this tool is quite useful for the Oracle DBA who inherits a database that was built by a beginner who over-indexes the tables without regard to the SQL that accessed the tables.

Here is a simple SQL*Plus script to turn-on index monitoring for your whole schema:

set pages 999

set heading off

spool run_mon.sql

select

   ‘alter index ‘||index_name||‘ monitoring usage;’

from

   dba_indexes

where

   owner = ‘SCOTT’;

spool off

@run_mon

Wait a bit, then check to see if the index has been used by a query:

select
   index_name,
   used
from
   v$object_usage
where
   owner = 'SCOTT';
 

In sum, the index monitoring feature has very little overhead and it is quite useful for locating and dropping unneeded Oracle indexes.

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