Finding redundant indexes is one thing and dropping them is another thing. This is why Petr has may be decided to check if those identified redundant indexes are used or not checking AWR and ASH The best thing to do then is to set the redundant indexes (or indexes contained in other indexes) in an invisble status (if using 11g or more) and monitor the application for a certain time. It is not because a redundant index is not found in AWR or ASH that it is safe to drop it. It might be that it is used by the CBO for its estimations or used by Oracle to cover a deadlock and lock threat introduced by an unindexed foreign key Best regards Mohamed Houri 2015-03-18 14:20 GMT+01:00 Timur Akhmadeev <timur.akhmadeev@xxxxxxxxx>: > I mean finding redundant/inefficient indexes with a bit of thinking about > logical structures. No need to look into AWR. Something like this > <http://pastebin.com/KBbDwGK8> as a starting point. > > On Wed, Mar 18, 2015 at 3:23 PM, Petr Novak <Petr.Novak@xxxxxxxxxxxx> > wrote: > >> Hallo Timur, >> >> >> >> for given index I usually list from shared pool all statements/plans >> which use the index, their benchmarks ( CPU/IO , number of executions). >> >> Then I check how effective are the execution plans, what are the access >> and filter predicates. >> >> That is the main source , but in the same way I look in AWR which >> statements/plans used the index in the past and what were the benchmarks. >> >> >> >> >> Best Regards, >> Petr >> >> >> >> ------------------------------ >> *Von:* Timur Akhmadeev [timur.akhmadeev@xxxxxxxxx] >> *Gesendet:* Mittwoch, 18. März 2015 12:16 >> *An:* Petr Novak >> *Cc:* jonathan@xxxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx >> *Betreff:* Re: Reduce latch row cache objects with event 10089 >> >> >> On Wed, Mar 18, 2015 at 9:08 AM, Petr Novak <Petr.Novak@xxxxxxxxxxxx> >> wrote: >> >>> I check continually index usage in shared pool and AWR, there are only >>> 1-2 index candidates for elimination. >> >> >> Do not need to look into AWR for that. Start with listing indexes & >> indexed columns instead, just looking at what is indexed + at >> avg_data_blocks_per_key. Very often people introduce redundant & >> unnecessary/inefficient indexes which could be logically eliminated. >> >> -- >> Regards >> Timur Akhmadeev >> > > > > -- > Regards > Timur Akhmadeev > -- Houri Mohamed Oracle DBA-Developer-Performance & Tuning Member of Oraworld-team <http://www.oraworld-team.com/> Visit My - Blog <http://www.hourim.wordpress.com/> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>* My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri <https://twitter.com/MohamedHouri>