WHY IS INDEX MONITORING REQUIRED?

Keeping a lot of indexes on a table, can affect the performance of the transaction like insert, update, delete on the table. i.e if we are inserting a new ROW, a new entry will be added to the indexes on that table. So the best way is to remove the unused indexes from the tables. But how will you find out what are the unused indexes?? Well, index monitoring feature will come to our rescue.

ALTER INDEX MONITORING USAGE; — Enable monitoring of the index
ALTER INDEX NOMONITORING USAGE; — Disable monitoring of the index

v$object_usage view tracks information about the index usage.

DEMO:

Created an index:

SQL> desc EMPTABLE2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(128)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(128)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 
 
SQL> create index IND_EMP on EMPTABLE2 (owner);

Index created.

— Run a query that will use the index .

SQL> select count(*) from EMPTABLE2 where owner='SYS';

  COUNT(*)
----------
     51473
	 

---------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |    66 |    39   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE       |         |     1 |    66 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IND_EMP | 55836 |  3598K|    39   (3)| 00:00:01 |
---------------------------------------------------------------------------------


	 

SQL> select INDEX_NAME,monitoring,USED,start_monitoring from v$object_usage where index_name='IND_EMP';

no rows selected

We can see, v$object_usage is not reporting any.

Lets enable monitoring for the index.

SQL> SQL>  alter index IND_EMP  monitoring usage;

Index altered.


SQL> set pagesize 200
set lines 929
col index_name for a18
col monitoring for a18
col used for a16
select INDEX_NAME,monitoring,USED,start_monitoring from v$object_usage where index_name='IND_EMP';
	
	
INDEX_NAME         MONITORING         USED             START_MONITORING
------------------ ------------------ ---------------- -------------------
IND_EMP            YES                NO               08/01/2017 12:57:57

MONITORING: YES ( It shows monitoring is enabled )
USED                :  NO (It shows index has not been used yet)

Lets run the same query that uses index:

SQL> select count(*) from EMPTABLE2 where owner='SYS';

  COUNT(*)
----------
     51473
	 


---------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |    66 |    39   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE       |         |     1 |    66 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IND_EMP | 55836 |  3598K|    39   (3)| 00:00:01 |
---------------------------------------------------------------------------------



SQL> select INDEX_NAME,monitoring,USED,start_monitoring from v$object_usage where index_name='IND_EMP';

INDEX_NAME         MONITORING         USED             START_MONITORING
------------------ ------------------ ---------------- -------------------
IND_EMP            YES                YES              08/01/2017 12:57:57

Now USED column is changed to YES. I.e someone has used the IND_EMP index.

To view the information in the V$OBJECT_USAGE view you have to be connected as the user owner of the objects you want to monitor, otherwise, you might not find the monitoring information you are looking for.

SQL> connect dbatest
Enter password:
all activities are audited
Connected.
SQL> select INDEX_NAME,monitoring,USED,start_monitoring from v$object_usage where index_name='IND_EMP';

no rows selected

As i have tried to access v$object_usage for the user dbatest(not the owner of that index), it is not returning any rows.

Well there is a workaround :

Create the view as below:( AS SYS USER)

 create or replace view ALL_OBJECT_USAGE
         (OWNER,
         INDEX_NAME,
         TABLE_NAME,
         MONITORING,
         USED,
         START_MONITORING,
         END_MONITORING)
         as
           select u.name, io.name, t.name,
           decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
           decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
           ou.start_monitoring,
           ou.end_monitoring
         from sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
         where i.obj# = ou.obj#
         and io.obj# = ou.obj#
         and t.obj# = i.bo#
         and u.user# = io.owner#;
		 
		 
SQL>
SQL> create public synonym ALL_OBJECT_USAGE FOR ALL_OBJECT_USAGE;

Synonym created.

Now try to view the information from ALL_OBJECT_USAGE table from dbatest user:

SQL> connect dbatest
Enter password:
all activities are audited

Connected.
SQL> select INDEX_NAME,monitoring,USED,start_monitoring fromcALL_OBJECT_USAGE  where index_name='IND_EMP';

INDEX_NAME         MONITORING         USED             START_MONITORING
------------------ ------------------ ---------------- -------------------
IND_EMP            YES                YES              08/01/2017 12:57:57

Voilla!!, I can see it 🙂

How to Disable monitoring:

SQL> alter index  IND_EMP no monitoring usage;

Index altered.

SQL> select INDEX_NAME,monitoring,USED,start_monitoring from v$object_usage where index_name='IND_EMP';

INDEX_NAME         MONITORING         USED             START_MONITORING
------------------ ------------------ ---------------- -------------------
IND_EMP            NO                 YES              08/01/2017 12:57:57

CONCLUSION: So to find to unused indexes in the database, Enable monitoring for the indexes in the database for a period of time, then get the usage report either from v$object_usage or ALL_OBJECT_USAGE. Analyze the report with application team and take a decision to remove the unused ones.