 | |
HowTo find out used (useful) and unused (usesless) indexes?
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#51
 
| |||
| |||
|
Re: HowTo find out used (useful) and unused (usesless) indexes? -
04-21-2008
, 02:46 PM
|
joel garry (joel-ga... (AT) home (DOT) com) wrote: : On Apr 18, 2:29=A0am, Andreas Mosmann <mosm... (AT) expires-30-04-2008 (DOT) 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 fora : > 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. |
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.
Quote:
|
"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. |
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/uniont...s_1n20mil.html
#52
| |||
| |||
|
|
joel garry (joel-ga... (AT) home (DOT) com) wrote: : On Apr 18, 2:29=A0am, Andreas Mosmann <mosm... (AT) expires-30-04-2008 (DOT) 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 fora : > 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. |
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.
Quote:
|
"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. |
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/uniont...s_1n20mil.html
#53
| |||
| |||
|
|
joel garry (joel-ga... (AT) home (DOT) com) wrote: : On Apr 18, 2:29=A0am, Andreas Mosmann <mosm... (AT) expires-30-04-2008 (DOT) 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 fora : > 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. |
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.
Quote:
|
"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. |
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/uniont...s_1n20mil.html
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.