Find unnecessary Indexes
There's hardly a relational database without an index. It's essential if you have a lot of data and also want to find records there. However, just adding an index without thinking isn't smart either.
Imagine an index like a table of contents of a book. If the book contains one chapter, why do you need an overview? Right, it's not really helpful if you already know that there's just a single value.
Recipe #1 - Find unnecessary Indexes
The query below will analyze your indexes and list of that have a selectivity less than 20%. If you have a table with 100'000 rows and only 5'000 distinct values, you'll have a selectivity of 5% (5'000 / 100'000 = 0.05 > 5%).
The ideal selectivity is 100%, the lower it gets, the less distinct values you have. Unfortunately, there's not a fixed selectivity percentage which can be considered as bad in every case. It usually depends on the records you generally select. If you have a table with 100'000 and use an index to query for 90'000 rows, the index isn't really useful. As a rule: If you query for less than 10% of the table's rows, you should quite likely add an index.
Of course, there are exceptions as always, if you just run that query once every century but have milions of write operations per day, it's also not really worth adding an index. Waiting a bit longer for the query to return can be better than slowing than write operations. Keep in mind that an index has to be updated if the records change.
Check this query to get a list of indexes which you should check and maybe remove:
SELECT di.owner, di.table_name, di.index_name, TO_CHAR((di.distinct_keys / di.num_rows) * 100, '999.99') selectivity, di.distinct_keys, di.num_rows, di.index_type FROM dba_indexes di WHERE di.num_rows > 0 AND di.distinct_keys / di.num_rows < 0.2 ORDER BY di.distinct_keys / di.num_rows