Re: Reduce latch row cache objects with event 10089

  • From: Mohamed Houri <mohamed.houri@xxxxxxxxx>
  • To: Timur Akhmadeev <timur.akhmadeev@xxxxxxxxx>
  • Date: Wed, 18 Mar 2015 19:41:25 +0100

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>

Other related posts: