RE: How many is too many

  • From: "Walker, Jed S" <Jed_Walker@xxxxxxxxxxxxxxxxx>
  • To: Kellyn Pot'vin <kellyn.potvin@xxxxxxxxx>, "veeeraman@xxxxxxxxx" <veeeraman@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 12 Aug 2011 19:54:22 +0000

Yes, but a simple loop I’ve found usually results in the lock being acquired 
after a bit (but not always).

I just monitored one of our oltp systems for several months and found 150+ 
indexes of 500 never used. Developers often create “what they think” is needed 
rather than what is needed – getting back to your earlier point about 
“sacrificing a small animal”

From: Kellyn Pot'vin [mailto:kellyn.potvin@xxxxxxxxx]
Sent: Friday, August 12, 2011 1:48 PM
To: Walker, Jed S; veeeraman@xxxxxxxxx; ORACLE-L
Subject: Re: How many is too many

When stating an index audit, monitoring was what I was referring to, but in 
10g, you have to get a lock on the object to turn on the monitoring...This can 
be the biggest hurdle, more so than the overhead of the index or monitoring for 

From: "Walker, Jed S" <Jed_Walker@xxxxxxxxxxxxxxxxx>
To: "veeeraman@xxxxxxxxx" <veeeraman@xxxxxxxxx>; ORACLE-L 
Sent: Friday, August 12, 2011 1:42 PM
Subject: RE: How many is too many
I don’t think it is so much a number as “is the index used”. You can use the 
“alter index [no]monitoring” command to turn on monitoring and then watch 
v$object_usage to see which are used. Make sure you observe over a good time 
period to ensure you don’t miss any reports that are not run too often.

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Ram Raman
Sent: Thursday, August 11, 2011 2:45 PM
Subject: How many is too many


  I am looking at a table in our system and it has 12 indexes, we are planning 
on adding another one. I am aware of the effects of having too many indexes, 
but in this case adding an extra index helps a certain query that runs slow. 
Other queries and most other operations against the table are acceptable too. I 
see a few tables like this; is there a number above which is considered a no-no 
when it comes to adding more indexes.

PS. The tables and queries are structured in a way that seem to require several 
indexes - it is a third party product.


Other related posts: