Re: index utilisation stats

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 18 Jul 2001 16:17:43 -0700
Message-ID: <9j55en02db3_at_drn.newsguy.com>


In article <3B560390.1416346D_at_maikrabe.de>, Maik says...
>
>Sorry. please send your answers to post_at_maikrabe.de!
>
>Thanks again!
>
>Maik Rabe schrieb:
>
>> Hello,
>>
>> how can i find indexes that are not used (on 8.1.6)?? I have found a new
>> method on 9i, but i cannot wait until we will migrate!
>>

Yes, in 9i you can alter the index and monitor its usage. You can then discover if it has been used to access the table in a given time period.

Prior to that, some ideas are:

o enable the capturing of query plans using "alter session set create_stored_outlines = 'some name'". That'll capture the detailed hints that approximate your query plan -- including all index hints. You can query the outline tables after some period of time to find all of the indexes referenced by hints. It is not 100% perfect as the same query can result in two different plans (if someone runs it with ALL_ROWS and someone else with FIRST_ROWS -- only one of the two sets of hints will be captured) but it can point you to indexes that are definitely used.

o place the indexes in their own tablespaces. Monitor the IO on these tablespaces. If the reads heavily outweigh the writes it is probably being used. If the reads are approximately the same as the writes or there is no reads at all, they are probably not used (just read to be maintained).

>> thanks
>>
>> Maik
>> Germany
>

--
Thomas Kyte (tkyte_at_us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 
Received on Thu Jul 19 2001 - 01:17:43 CEST

Original text of this message