Monitoring Index to Overhead Unnecessary Statement

We can monitor our indexes to determine whether they are being used or not. If we found the indexes are not being  uses we can disable this for reduce database Overhead.


SQL> CREATE TABLE INDEX_MONITOR (ID NUMBER, NAME VARCHAR2(300));

Table created.

SQL> CREATE INDEX IND_INDEX_MONITOR_ID ON INDEX_MONITOR(ID);

Index created.

SQL> ALTER INDEX IND_INDEX_MONITOR_ID MONITORING USAGE;

Index altered.

SQL> INSERT INTO INDEX_MONITOR VALUES(10,'RAJIB');

1 row created.

SQL> INSERT INTO INDEX_MONITOR VALUES(20,'RAJIB');

1 row created.

SQL> INSERT INTO INDEX_MONITOR VALUES(30,'RAJIB');

1 row created.

SQL> INSERT INTO INDEX_MONITOR VALUES(40,'RAJIB');

1 row created.

SQL> SELECT * FROM INDEX_MONITOR;

        ID
----------
NAME
--------------------------------------------------------------------------------
        10
RAJIB

        20
RAJIB

        30
RAJIB


        ID
----------
NAME
--------------------------------------------------------------------------------
        40
RAJIB


SQL> SELECT * FROM V$OBJECT_USAGE;

INDEX_NAME                     TABLE_NAME                     MON USE
------------------------------ ------------------------------ --- ---
START_MONITORING    END_MONITORING
------------------- -------------------
IND_INDEX_MONITOR_ID           INDEX_MONITOR                  YES NO
08/25/2014 18:04:45


SQL> SET LINE 2000
SQL> SET LINE 2000
SQL> SELECT * FROM V$OBJECT_USAGE;

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
IND_INDEX_MONITOR_ID           INDEX_MONITOR                  YES NO  08/25/2014 18:04:45

SQL> SELECT * FROM INDEX_MONITOR WHERE ID=10;

        ID NAME
---------- ------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
        10 RAJIB

SQL> SELECT * FROM V$OBJECT_USAGE;

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
IND_INDEX_MONITOR_ID           INDEX_MONITOR                  YES YES 08/25/2014 18:04:45

SQL>