Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to determine unused indexes

Re: How to determine unused indexes

From: Pete Sharman <peter.sharman_at_oracle.com>
Date: 22 Mar 2002 21:07:41 -0800
Message-ID: <a7h2it0gjg@drn.newsguy.com>


In article <a7gsml$rhj$1_at_lust.ihug.co.nz>, "Howard says...
>
>There's an article on exactly this subject at Jonathan Lewis's hosted Oracle
>FAQ. There's link to Jonathan's site on my site (which also happens to
>contain an article on the subject).
>
>Not sure if either article mentions it, but there's a;ways the possibility
>of rebuilding an index into a tablespace of its own.... using utlbstat and
>utlestat, or using v$filestat, it's then possible to determine the
>reads/writes on that tablespace: lots of reads, the index is useful. Lots
>of writes, the table is being updated a lot, so there's a lot of index
>maintenance to do.
>
>Regards
>HJR
>--
>----------------------------------------------
>Resources for Oracle: http://www.hjrdba.com
>===============================
>
>
>"Michael Gill" <mydba_at_usa.com> wrote in message
>news:fb510dcd.0203221912.606baa6f_at_posting.google.com...
>> 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
>
>

And the other approach that can be taken is to use the ALTER INDEX MONITORING USAGE command. Only problem is that it's a 9i command, but if you've got 9i you're set. I think there's also an index advisory (or something similarly named) in OEM to help with this.

HTH. Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Fri Mar 22 2002 - 23:07:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US