Re: HowTo find out used (useful) and unused (usesless) indexes?

From: joel garry <joel-garry_at_home.com>
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.html
Received on Mon Apr 21 2008 - 13:46:53 CDT

Original text of this message