How to know which indexes are never used

[raztaboule]

does anyone knows a query to list indexes that are not used ?

the context is : Some users are complaining about performance. db_block_buffer used ratio is high (95%) , row_cache used ratio is also high (91%) , in average about 10M are free in the shared pool, no paging occurs on the system, the only thing is that the IO on the disk where indexes are stored is very high regarding to the activity of other disks.
so i was wondering if some indexes where unused to delete them and make updates more "light" .

thanks to all.

 

[parbhani]

Hi ,

I do not think that un-used indexes will make any difference. But it the I/O is high on index disk, there is a possibility that your indexes are not wel-designed.

Means, if index-range-scans are scanning thousands of records then this may happen. So , see if there are any indexes on which huge range scans are happenning.

Either change the order of columns in the index.

Also, are you using /*+rule */ hint in the queries. This sometimes cause un-nessesary use of indexes.

Have a thought.

Cheers

 

[raztaboule]

thanks i will try to check that. I'm not really involved in the designed/develloppement of the application so i have to check for the points you underlined.
I fact i was wondering about number of indexes because i notice writes I/O.

thanks,
bye.