Re: Tracking usage of index

From: Jonathan Lewis <jlewisoracle_at_gmail.com>
Date: Fri, 27 Nov 2020 10:05:48 +0000
Message-ID: <CAGtsp8n_hyi5CRTQtUs+R_RZiQH_BKYETGeqfipsGNPJ=n_LyA_at_mail.gmail.com>



You can almost guarantee that there won't be any new option in 19.3 when the latest strategy only appeared in 12.2. It's disappointing that the stats collection usage hasn't been disabled. However, one of the usage buckets is "> 1000", so for any big indexes that are not needed you could take the view that seeing that increment once per day, or once per week is an indication that it's a stats collection usage, and you could correlate this with the the optimizer stats history of operations to check that each usage correlates with stats collection.

Regards
Jonathan Lewis

On Fri, 27 Nov 2020 at 07:50, Lok P <loknath.73_at_gmail.com> wrote:

> yes, but i was thinking as we are in version 19C, and i see in the blog
> stating, the index usage is now by default taken care by Oracle starting
> version 12.2 and the details gets flushed into DBA_INDEX_USAGE view. And no
> need to set the index monitoring ON specifically as it used to happen in
> 11.2.
>
> But the issue is, when I tried collecting stats on the index , even then
> the DBA_INDEX_USAGE was getting populated which is wrong. It should only be
> populated when it really gets accessed for fetching data like SELECT
> queries etc. So wanted to understand , if any other way through which we
> will get the index usage correctly(may be new in 19.3)? Also foreign key
> access is not captured in this case, and dropping those indexes will be
> dangerous.
>
> https://oracle-base.com/articles/12c/index-usage-tracking-12cr2
>
> On Fri, Nov 27, 2020 at 3:59 AM Mladen Gogala <gogala.mladen_at_gmail.com>
> wrote:
>
>> Hi Lok,
>>
>> You can turn on monitoring for index, something like "ALTER INDEX PK_EMP
>> MONITORING USAGE". The usage will be in V$OBJECT_USAGE. Unfortunately,
>> there is no owner in that table, so you need to log in as the index owner.
>>
>> Regards
>> On 11/26/20 3:19 PM, Lok P wrote:
>>
>> We have just moved from Oracle version 11.2.0.4 to 19.3. And also we were
>> on HP and now we moved to Exdata-X5. And the team is asking to verify if
>> some of the indexes are actually not in use as we are in Exadata and thus
>> we can drop them afterwards. So was trying to understand the reliable way
>> to achieve the list of unused indexes?
>>
>> As per my understanding 11.2.0.4 was having option for setting the
>> monitoring On for the specific indexes, to see its usage from
>> dba_object_usage, but that was just a "YES" or "NO" flag which was not of
>> much help(as amount of usage is also not captured there) and also stats
>> gathering on the index was making the usage flag to YES which is wrong,
>> also indexes on foreign key were not getting picked up as USED in cases.
>>
>> But i see starting from version 12.2+ , Oracle is by-default capturing
>> all the information in a new view DBA_INDEX_USAGE, so wanted to understand
>> from experts if this is safe and reliable way to collect index usage
>> information or if we have anything new on 19C more reliable for finding
>> index usage?
>>
>> --
>> Mladen Gogala
>> Database Consultant
>> Tel: (347) 321-1217
>>
>>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Nov 27 2020 - 11:05:48 CET

Original text of this message