Indexes are the easiest solution for fast query performance improvement but the gain does not come without a cost. Indexes take up space and make inserts slower. In Addition, many times, the index is added to solve one slow query. If the query is not executed frequently, the performance improvement might not be worth the cost of making almost every insert and update slower. For example, even if the index is useful for a nightly or weekly report, the extra daily overhead might not be worth the few minutes it will save during the night.
therefore, it is important to know the if and how much the index is used. The tool for this job is index monitoring usage.
Basically the process is very simple. The first step is turning on monitoring since index monitoring usage is turned off by default. The next step is to let it run for a while. It is important to choose the monitoring period wisely as it supposed to correctly represent real life usage. After that you can turn off monitoring and query the usage view.
Turing on index monitoring for a specific index:
ALTER INDEX “YOUR-SCHMA-NAME”.”YOUR-INDEX-NAME” MONITORING USAGE;
If you get: ora-00054 resource busy and aquire with NOWAIT specified or timeout expired
This is because altering a table or an index requires an exclusive lock on the table therefore it will not work while DML operations are performed on the table
Turing on index monitoring for all indexes on all indexes for a specific schema:
for i in ( select * from dba_indexes d where d.owner ='YOUR_SCHEMA' and index_type!='LOB' --and index_name='ASSIGNMENT_TERR'
query_str:='ALTER INDEX "' || i.owner || '"."' || i.index_name || '" NOMONITORING USAGE' ;
execute immediate query_str ;
Without adding the index_type!=’LOB’ you might get ORA-22864 cannot ALTER or DROP LOB indexes which means that you can not operate directly on a system-defined LOB index. You should perform operations on the corresponding LOB column.
Turing OFF index monitoring for a specific index:
ALTER INDEX "YOUR-SCHMA-NAME"."YOUR-INDEX-NAME" NOMONITORING USAGE;
You can analyze the results by running
select * from v$object_usage
several point to take into consideration:
don’t rush to drop the index if you see that it wasn’t used. It is possible that that it is used only once a month but it can still be crucial.
If you turn off monitoring and turning it on again, previous data is deleted. Therefore it might be wise to backup v$object_usage from time to time.
After you drop an index, the indesx data is deleted from v$object_usage