Re: HowTo find out used (useful) and unused (usesless) indexes?
Date: Mon, 21 Apr 2008 11:46:53 -0700 (PDT)
Message-ID: <c9f13fd0-521c-4721-a2eb-7489673c8c44@n14g2000pri.googlegroups.com>
On Apr 18, 4:01 pm, yf..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones)
wrote:
> joel garry (joel-ga..._at_home.com) wrote:
>
> : On Apr 18, 2:29=A0am, Andreas Mosmann <mosm..._at_expires-30-04-2008.news-: group.org> wrote:
>
> : > Thank both of you,
> : >
> : > I will try it out.
> : > Is there also a way to determine what index is still needed/useful for a
> : > special query?
> : >
> : > Andreas Mosmann
> : >
> : > --
> : > wenn email, dann AndreasMosmann <bei> web <punkt> de
>
> : I do believe that is the downside of deleting indices based on usage.
> : It only shows what's been used during the observation. That implies a
> : bad assumption that the usage is completely stable. To me, this seems
> : worse than just dropping an index and seeing who screams, since when
> : there is a problem in the future, you have to go through an entire
> : performance tuning workup because the linkage to the act of dropping
> : the index is obscured. Maybe I'm missing the concept. What about an
> : index that would be used when you pass some tipping point or boundary
> : condition or upgrade or change a session parameter?
>
> You can disable an index. That way the definition exists but the index is
> never used or maintained (i.e. no overhead). If you decide it is needed
> you simply enable it.
But that's my point. The decision is being made on past performance, why would you decide it is needed? Certiainlly if you are using method-r the users would be complaining about it far too late, it could have been needed for years between the time it was dropped and the time a complaint is made - people tend to think "that's how the system works" and not complain if something gets slower slowly. I still don't see what synapses would have to be fired (thanks for that one, Dan!) to make this tool useful. In my experience, either the system has been well-vetted over time (like an enterprise system sold to many customers), or it's newly developed and the thought have been tested and decisions made (or, it's just a crappy system). It ought to be useful for the enterprise, as vendors can't know what parts of the system the customer will use, but I haven't seen much of that, as a DBA I've only seen missing indices, and sometimes it takes quite a bit of work to figure that out. Has anybody actually found this tool useful? (not a rhetorical question, I'm curious, and always wondering about it when I see some new feature trumpeted everywhere, but not success stories. Of course, I don't see everything.).
Since it does take work to figure missing indices out, I can't help but wonder if this tool is counterproductive, by making it too quick on the draw to delete indices, making more work later. If you have to make a big project out of it with dbms_stats, with no one complaining about performance... in a complicated enterprise system, you may not know how the future data growth will be impacted by business changes and software upgrades.
>
> "when you pass some tipping point"
>
> If an index is enabled then presumably it will only be used when the CBO
> decides it is useful for a query.
Again, that's my point. The decision is being made before the CBO can decide that.
jg
-- @home.com is bogus. “It was them saying, 'We need to stick our hands up your back and move your mouth for you.' ” - Robert Bevelacqua, retired Army Green Beret and former Fox News analyst. http://www.signonsandiego.com/uniontrib/20080420/news_1n20mil.htmlReceived on Mon Apr 21 2008 - 13:46:53 CDT