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.
🛩️ Shane Borden's Technology Blog 