Index Monitoring

Oracle9i has introduced an easy method to see of indexes are even being used by your application.

Amazing as it sounds, I am finding an average of 10 indexes per table object, particularly in larger applications. Oracle probably is not using most of these indexes or only uses them occasionally such as at month end/quarter end/year end processing. These unused indexes are maintained and can make batch cycles run twice/three times as long as needed.

My SQL Performance Tuning class (being offered frequently via the Web…or we can schedule this at your company) goes into great detail on how Oracle uses indexes, how the optimizer decides which index to use, as well as this index-monitoring topic…which is an excerpt from my SQL Performance Tuning course guide.

Index Monitoring


Oracle has the ability to tell if an index has ever been used in a SELECT or sub-query. This technology uses the MMON process and has to be tripped on to populate the V$OBJECT_USAGE view.

SQL> desc v$object_usage
Name Null? Type
----------------------------------------- -------- --------------------
INDEX_NAME NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
MONITORING VARCHAR2(3)
USED VARCHAR2(3)
START_MONITORING VARCHAR2(19)
END_MONITORING VARCHAR2(19)

SQL>

If the index then appears in a query explain plan, the USED column will be set to ‘YES’, otherwise it is ‘NO’. Make sure to connect as the object owner. Only objects owned by the connected user will be visible in this view.


This script can be used to turn on index monitoring for all indexes for a particular schema owner: Start_Index_Monitoring.sql …is a SQL creating SQL script that turns index monitoring on for each index for the schema owner
Index_Monitoring.sql produces a nice report (see below) of the counts.
1. Indexes that were used
2. Indexes that were not used
3. Number of indexes being monitored
4. Number of tables that have the indexes

Ask me for these scripts...

spool Index_Monitor_setup.sql
select 'Alter Index ' index_name ' monitoring usage;' from user_indexes;

spool off
start Index_Monitor_setup.sql

Portion of Index_Monitor_Setup.sql script.


select to_char(sysdate,'mm/dd/yy hh:mi:ss') Date_Time from dual;
select 'Table Count = ' count(*) from user_tables;
select 'Index Count = ' count(*) from v$object_usage;
Select 'Indexes Used = ' count(*) from v$object_usage where USED = 'YES';
select 'Indexes NOT Used = ' count(*) from v$object_usage where USED = 'NO';

Portion of Index_Monitor.sql Script


Index Monitoring Report Date: 02/14/10 10:33:37

Table Count = 27

Index Count = 23

Indexes Used = 3

Indexes NOT Used = 20

Index Monitor Report



Ask me for these scripts (Dhotka@Earthlink.net).

Eliminating unused indexes is batch cycle tuning. Indexes and table structures have 5 Oracle operations applied to each DML:
Lock acquired
Before image journalled in rollback segment
Actual change applied
After image journalled to archive log
Lock released

…for each object associated with the DML. If the index is not being used, then it should not be there as it is being maintained by Oracle.

*** True Story *** A shop in Denver had 98 tables and over 1100 indexes! I tripped on index monitoring and when I left 3 days later, Oracle had not used 100 indexes yet.

*** Tip *** The programmer should use this technique when testing an application and build scripts that create/drop indexes before month end/quarter end/year end processing.

Summary:
Index monitoring is a useful tool to eliminate unused indexes that will decrease batch cycle times and decrease insert/update times.
My SQL Performance Tuning class has a very interesting half-day lecture on indexes, how they are created, how Oracle uses them, when they are not used and why, and how 10g+ tablespace options has an adverse affect on index utilization.