When I think about indexes in any DBMS, I immediately think about the lyrics in the Eagles song “Hotel California”.

“You can check-out any time you like, but you can never leave…..”

Almost every system that has been around for a while is over indexed. Every index costs something to maintain and some more than others. Also, systems and code change. There could be an index that was once needed, but over time the code shifts to use another one thats more efficient instead. Guess what though, you are still maintaining that old index. That costs CPU, I/O which translates into TIME.

Thankfully in Oracle 12.2, they have introduced a feature called INDEX USAGE TRACKING. In older versions this was called INDEX MONITORING and it was much harder and invasive to use. In the newer versions, it just works and you can read more about both options here:

https://oracle-base.com/articles/12c/index-usage-tracking-12cr2

By default, indexes in the views “DBA_INDEX_USAGE” and “V$INDEX_USAGE_INFO”. The “V$” table is flushed to the “DBA” table every 15 minutes. For most purposes, using the “DBA” table is just fine. The thing to remember is that querying only these tables tell you the indexes that are being USED, not the ones that are NOT USED. To get the ones that are not used, we just need to expand the query a bit:

SELECT
    di.owner,
    di.index_name,
    di.index_type,
    di.table_name
FROM
    dba_indexes di
WHERE
             di.owner in (select username from dba_users where oracle_maintained = 'N')
    AND di.uniqueness = 'NONUNIQUE'
    AND NOT EXISTS (
        SELECT
            1
        FROM
            dba_index_usage iu
        WHERE
                iu.owner = di.owner
            AND iu.name = di.index_name
    )
GROUP BY
    di.owner,
    di.index_name,
    di.index_type,
    di.table_name
ORDER BY
    di.owner,
    di.table_name,
    di.index_name;

Now that we have the indexes that are not used, which ones have the biggest impact on database operations? We just expand the query again to leverage the “DBA_TAB_MODIFICATIONS” table. This will allow us to see how many operations occur on each table that the index sits on:

SELECT
    di.owner,
    di.index_name,
    di.index_type,
    di.table_name,
    SUM(tm.inserts + tm.updates + tm.deletes) operations
FROM
    dba_indexes di,
    dba_tab_modifications tm
WHERE
             di.owner in (select username from dba_users where oracle_maintained = 'N')
    AND di.owner = tm.table_owner
    AND di.table_name = tm.table_name
    AND di.uniqueness = 'NONUNIQUE'
    AND NOT EXISTS (
        SELECT
            1
        FROM
            dba_index_usage iu
        WHERE
                iu.owner = di.owner
            AND iu.name = di.index_name
    )
GROUP BY
    di.owner,
    di.index_name,
    di.index_type,
    di.table_name
ORDER BY
    SUM(tm.inserts + tm.updates + tm.deletes) ASC NULLS FIRST,
    di.owner,
    di.table_name,
    di.index_name;

Now, I have done an equi-join condition against the DBA_TAB_MODIFICATIONS so this is only showing us unused indexes against tables that the system has recorded operations against. If you wanted to show all indexes regardless if there were operations or not, then just change the join condition against DBA_TAB_MODIFICATIONS to be an outer join.

In my case, there were some very large indexes that were unused sitting on tables that had LOTS of operations (names changed to protect the innocent):

OWNER	INDEX NAME	INDEX TYPE	TABLE NAME	OPERATIONS
HR	FK_1_IDX	NORMAL	EMPLOYEES	295931545
HR1	EMP_TOT	FUNCTION-BASED NORMAL	EMP_HOURS	1374288673
HR2	EMP_TOT	FUNCTION-BASED NORMAL	EMP_HOURS	1425114200
HR3	EMP_TOT	FUNCTION-BASED NORMAL	EMP_HOURS	2487284020

Adding “OPERATIONS” to the query now gives us some sense of the “BENEFIT” of dropping the index. Based on the results you can present your case for dropping the index. People always love to have the “BENEFIT” quantified and this is the closest thing you can do without performing a session trace which could prove to be invasive and fairly difficult to get the exact statement you are looking for.

Hopefully this will give you the additional information you need to advocate for dropping that index that is no longer being used.