RE: How many is too many
Date: Fri, 12 Aug 2011 20:55:28 +0000
Message-ID: <BAA6E28B6241F046AED1E62D8697516C4E393EE1_at_COPDCEXMB08.cable.comcast.com>
Sometimes when you try to turn on index monitoring you'll get a NOWAIT trying to get the lock to set the monitoring (I think Kellyn might have indicated this isn't the case in 11g). Chances are if you try hard enough you'll catch it at the right moment to get the lock and make the changes. So, you can create a simple loop something like the following (excuse the pseudo-code, I don't have an example with me right now), either shell or pl/sql. You might think because it is busy and you can't get the lock it is being used, but that could just be index maintenance (anyone correct me if I'm wrong there)
Changed:=false
<loop>
Begin
Alter index example_idx monitoring usage;
Changed:=true;
Exception
If <nowait> then null;
End;
<end loop when changed=true>
From: Ram Raman [mailto:veeeraman_at_gmail.com]
Sent: Friday, August 12, 2011 2:37 PM
To: Walker, Jed S
Cc: Kellyn Pot'vin; ORACLE-L
Subject: Re: How many is too many
Jed,
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<mailto: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<mailto:kellyn.potvin_at_ymail.com>]
Sent: Friday, August 12, 2011 1:48 PM
To: Walker, Jed S; veeeraman_at_gmail.com<mailto: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<mailto:Jed_Walker_at_cable.comcast.com>> To: "veeeraman_at_gmail.com<mailto:veeeraman_at_gmail.com>" <veeeraman_at_gmail.com<mailto:veeeraman_at_gmail.com>>; ORACLE-L <oracle-l_at_freelists.org<mailto: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> [mailto: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-lReceived on Fri Aug 12 2011 - 15:55:28 CDT