Monitoring Indexes

Databases, over the period of time can have many indexes. Some indexes might be in use but others may not be. Since these indexes eat up al lot of space and adds to a total cost, we should try to check what all indexes are in use and what not so that we can remove the unwanted indexes from the system.

Way to acheive this is that, we can turn on the Monitioring on the indexes for a while like -

ALTER INDEX INDEX_NAME MONITORING USAGE;

and once the information has been gathered we can turn off the monitoring like -

ALTER INDEX INDEX_NAME NOMONITORING USAGE;

This will record all the information under the table OBJECT_USAGE data dictionary view.
Under this view there are following four column (OBJ#,FLAGS,START_MONITORING,
END_MONITORING)


OBJ# is the OBJECT_ID and can be correlated with DBA_OBJECTS. The OBJECT_ID is a unique identifier of the object in the database. Each object is assigned a unique number to recognize it in the database. So, no two object of two different schemas will be same. That is why Oracle might have kept only one master table (X$) and one master view (OBJECT_USAGE) to know which objects were actually get used and which were not.

FLAGS column of OBJECT_USAGE view can have two values, 1 (USED) and 0 (NOT USED).


After turning on/off the monitoring we can run a simple query like the following to get the Objects that are being in use -

SELECT A.OWNER, A.OBJECT_NAME, A.OBJECT_TYPE
FROM DBA_OBJECTS A, OBJECT_USAGE B
WHERE A.OBJECT_ID=B.OBJ#
AND B.FLAGS=1
/

If you want to check the space used by all INDEXS that are NOT in use then you can check it by running this query -

SELECT SUM(BYTES/1024/1024/1024) "TOTAL SPACE (GB)"
FROM DBA_SEGMENTS A, DBA_OBJECTS B, OBJECT_USAGE C
WHERE A.OWNER=B.OWNER
AND A.SEGMENT_NAME=B.OBJECT_NAME
AND B.OBJECT_ID=C.OBJ#
AND B.OBJECT_TYPE='INDEX'
AND C.FLAGS=0
/


This can save us not only space but can enhance the performance of the query also.