Re: How many is too many

Thanks for the reply.

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

Thanks.

On Fri, Aug 12, 2011 at 2:54 PM, Walker, Jed S <Jed_Walker_at_cable.comcast.com
> wrote:

> 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_at_ymail.com]
> *Sent:* Friday, August 12, 2011 1:48 PM
> *To:* Walker, Jed S; veeeraman_at_gmail.com; 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 usage.****
>
> Kellyn****
>
> ** **
> ------------------------------
>
> *From:* "Walker, Jed S" <Jed_Walker_at_cable.comcast.com>
> *To:* "veeeraman_at_gmail.com" <veeeraman_at_gmail.com>; ORACLE-L <
> oracle-l_at_freelists.org>
> *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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Ram Raman
> *Sent:* Thursday, August 11, 2011 2:45 PM
> *To:* ORACLE-L
> *Subject:* How many is too many****
>
> ****
>
> Listers,****
>
> ****
>
> 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. ****
>
> ****
>
> TIA,****
>
> Ram.****
>
> ** **
>

--
http://www.freelists.org/webpage/oracle-l