Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Vic.

Asked: November 29, 2018 - 11:37 am UTC

Last updated: November 30, 2018 - 3:24 pm UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hi All,

I know index monitoring in 12.1.0.2 is not that great out of the box. I have a query that runs against the DBA_HIST_SQL_PLAN view, as below. Would this be a suitable way to get index usage? (We have the Diag+Tuning packs available to us)

 WITH Q AS (
                SELECT
                       S.OWNER                  A_OWNER,
                       TABLE_NAME               A_TABLE_NAME,
                       INDEX_NAME               A_INDEX_NAME,
                       INDEX_TYPE               A_INDEX_TYPE,
                       SUM(S.bytes) / 1048576   A_MB
                  FROM DBA_SEGMENTS S,
                       DBA_INDEXES  I
                 WHERE S.OWNER =  '&&OWNER'
                   AND I.OWNER =  '&&OWNER'
                   AND INDEX_NAME = SEGMENT_NAME
                 GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE
               
        )
        SELECT /*+ NO_QUERY_TRANSFORMATION(S) */
               A_OWNER                                    OWNER,
               A_TABLE_NAME                               TABLE_NAME,
               A_INDEX_NAME                               INDEX_NAME,
               A_INDEX_TYPE                               INDEX_TYPE,
               A_MB                                       MB,
               DECODE (OPTIONS, null, '       -',OPTIONS) INDEX_OPERATION,
               COUNT(OPERATION)                           NR_EXEC
         FROM  Q,
               DBA_HIST_SQL_PLAN d
         WHERE
               D.OBJECT_OWNER(+)= q.A_OWNER AND
               D.OBJECT_NAME(+) = q.A_INDEX_NAME
        GROUP BY
               A_OWNER,
               A_TABLE_NAME,
               A_INDEX_NAME,
               A_INDEX_TYPE,
               A_MB,
               DECODE (OPTIONS, null, '       -',OPTIONS)
        ORDER BY
               A_OWNER,
               A_TABLE_NAME,
               A_INDEX_NAME,
               A_INDEX_TYPE,
               A_MB DESC,
               NR_EXEC DESC
/


Regards,

Vic



and Chris said...

The AWR tables only capture details about "top" SQL statements. e.g. longest running, most CPU, most I/O, etc.

So fast, infrequently run statements won't appear. The kind that probably use an index you want to keep!

So I'd be wary of using this method as a way to identify unused indexes.

Remember: even with the 12.2 improvements to index monitoring, you can only prove that an index is used. It's hard to verify an index is never used.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Vic Cordiner, November 29, 2018 - 2:26 pm UTC

Thanks very much for your concise answer.

Vic

neve use index justify usinf dictionary view

Vinay, November 30, 2018 - 6:13 am UTC

Thanks,
as per you said
"Remember: even with the 12.2 improvements to index monitoring, you can only prove that an index is used. It's hard to verify an index is never used. ".

I have question that is Why hard to verify an index is never used.?

As I assume, If when we monitor an index so that if total number of index is 200. and i monitoring 200 index using
"ALTER INDEX index_name_1_to_200 MONITORING USAGE;"

Can I take decision as per following dictionary views which index is never used?
DBA_INDEX_USAGE
dba_object_usage
v$object_usage
v$index_usage_info
Connor McDonald
November 30, 2018 - 6:48 am UTC

For example

1) v$object_usage

If the index is used as part of an on-delete-cascade, it won't show up here

2) DBA_INDEX_USAGE

It is a *sampling* algorithm. Rare or sporadic usage might not appear.

If an index is used to satisfy reduced locking for a foreign key validation, it won't show up here.

So...these tools are much better than having nothing, but they don't replace the need to have a brain making some informed decisions behind it

Index use at the end of the year

David DUBOIS, November 30, 2018 - 11:11 am UTC

When you said : "It's hard to verify an index is never used.", I agree with you but because of entreprises rules.

For exemple, you are monitoring your indexes during a whole month (January for exemple) and you decide to drop all unused indexes ==> bad luck, you deleted indexes used in applications that run only in december or at the end of a quarter...

So it is really hard to say that this index is never used.
Chris Saxon
November 30, 2018 - 3:24 pm UTC

Yep, great point. And even if you monitor for over a year, changes in your data and/or application may mean that indexes not used in the quarterly/annual reports this time around are needed next year.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.