How to monitor whether Oracle indexes are used

During many software development processes, you do not need to properly plan indexes. As a result, a table has more than N indexes, which may cause troubles for subsequent maintenance and optimization. Therefore, you sometimes need to monitor whether existing indexes are in use. Oracle provides a tool to monitor whether indexes are used. This is a simple introduction.

First, if we monitor all the indexes on a table, we can use the following command to monitor it:

SQL> select 'alter Index' | index_name | 'monitoring usage; 'from user_indexes where table_name = upper ('mpaymentappl ');

 

'Alterindex' | index_name | 'monitoringusage ;'
------------------------------------------------------------
Alter index idx_mpaymentappl_bcode monitoring usage;
Alter index mpaymentappl_flags monitoring usage;
Alter index mpaymentappl_response code monitoring usage;

 

Then execute these scripts to start monitoring. You can view the monitoring information through V $ object_usage and check whether the index is used through the used column:

 

SQL> select * from V $ object_usage;

Index_name table_name mon use start_monitoring end_monitoring
--------------------------------------------------------------------------------------------------------
Idx_mpaymentappl_bcode mpaymentappl Yes No 03/24/2010 10:55:27
Mpaymentappl_flags mpaymentappl Yes No 03/24/2010 10:55:27
Mpaymentappl_democode mpaymentappl Yes No 03/24/2010 10:55:28

 

Unmonitor an index:

SQL> alter index mpaymentappl_flags nomonitoring usage;

Index altered.

SQL> select * from V $ object_usage
2/

Index_name table_name mon use start_monitoring end_monitoring
--------------------------------------------------------------------------------------------------------
Idx_mpaymentappl_bcode mpaymentappl Yes No 03/24/2010 10:55:27
Mpaymentappl_flags mpaymentappl no 03/24/2010 10:55:27 03/24/2010 10:57:19
Mpaymentappl_democode mpaymentappl Yes No 03/24/2010 10:55:28

 

 

Indexes that are not used for a certain period of time can be deleted to improve the efficiency of DML operations.

 

 

In practice, we can temporarily disable the index function to improve the efficiency of data table insertion and modification. Because, when the index works, DML operations on a large amount of data will bring about a lot of index updates and redo log generation. This is not required during batch data loading. Therefore, you can temporarily disable indexes.