Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to determine unused indexes
Explan plan all the sql statement in your sqlarea can be done automatically.
The artical can be found at oracle magzine, Nov/Dec 2001. There is a pl/sql
script by Don XXX. He also has a book called performance tuning by
statspack (something similar to this), there is a good detail about this. I
set it up at my site, which is a very powerful techniche, you can get all
the index usage, full table scan, index range scans, etc,etc. I highly
recommend it.
Let me know if you need more help.
"Sean M" <smckeownNO_at_BACKSIESearthlink.net> wrote in message
news:3C9C11FA.6DF39E91_at_BACKSIESearthlink.net...
> Michael Gill wrote:
> >
> > Given that I have numerous indexes, how can I determine which ones are
> > NOT used. I could check each query with explain plan, but is there an
> > easier method? Is there some v$ table that counts hits on an index?
> >
> > Thanks
>
> If you're using 9i, there's a new feature called index monitoring which
> you may find useful. See the article "Using Oracle9i to Clean Up
> Indexes" in the last issue of Oracle Magazine:
>
> http://www.oracle.com/oramag/oracle/02-jan/index.html?o12o9index.html
>
>
> Regards,
> Sean
Received on Sat Mar 23 2002 - 10:15:53 CST