Re: Tracking usage of index

Thank You Jonathan for the trick of comparing usage bucket count with the optimizer stats collection history.

On Fri, Nov 27, 2020 at 3:36 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> 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