 | |
HowTo find out used (useful) and unused (usesless) indexes?
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#21
 
| |||
| |||
|
Re: HowTo find out used (useful) and unused (usesless) indexes? -
04-18-2008
, 07:01 PM
#22
| |||
| |||
|
|
joel garry (joel-garry (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 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. "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. |
Shakespeare
#23
| |||
| |||
|
|
joel garry (joel-garry (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 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. "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. |
Shakespeare
#24
| |||
| |||
|
|
joel garry (joel-garry (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 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. "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. |
Shakespeare
#25
| |||
| |||
|
|
joel garry (joel-garry (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 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. "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. |
Shakespeare
#26
| |||
| |||
|
|
I just don't get it. This looks like a feature capriciously useful for poorly implemented systems. jg |
of the tool. It is just that the tool, like all tools, requires some
application of a synapse or two.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#27
| |||
| |||
|
|
I just don't get it. This looks like a feature capriciously useful for poorly implemented systems. jg |
of the tool. It is just that the tool, like all tools, requires some
application of a synapse or two.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#28
| |||
| |||
|
|
I just don't get it. This looks like a feature capriciously useful for poorly implemented systems. jg |
of the tool. It is just that the tool, like all tools, requires some
application of a synapse or two.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#29
| |||
| |||
|
|
I just don't get it. This looks like a feature capriciously useful for poorly implemented systems. jg |
of the tool. It is just that the tool, like all tools, requires some
application of a synapse or two.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#30
| |||
| |||
|
|
But an index may become useful over time, true? Shakespeare |
column of every table so I will respectfully disagree unless you
write a very broad definition of "may."
You need to understand your data and how it is being accessed.
The extra overhead of an unused index is not value added.
My recommendation would be to use the DBMS_STATS.SET.... procedures
to see how queries will react to the expected future growth of both
tables and indexes.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
|
« 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.