-
If you have Oracle Enterprise Manager with Diagnostic Pack which includes Oracle Expert, this will generate a report which would recommend where you have to delete unused indexes, where you need to add index (this is just a little part of report)etc etc . I believe you can download OEM from technet so diagnostic pack.
-
There is another tool which I wrote called EZSQL (www.ezsql.net) which will go through the sql statements in v$SQLAREA and analyze them, and figure out which indexes have been used and which ones have not. You can set up some filters so it doesn't analyze everything in V$SQLAREA, just the ones you want...
-
Oracle Expert goes through v$sqlarea as well.