What is index monitoring?

*in oracle databases consists of several custom indexes which we are created different types of indexes to get faster result retrival.

*The creation of unnecessary index of tables consume space.

*The best way to find out the used/unused index is "index monitoring "

* we can enable "index monitoring"(to monitor) on any index of any schema.

To View Index Columns:

 

SQL> select * from user_ind_columns where index_name='INDEX NAME';

Note:

*dba_ind_columns
            the column of indexes on all the tables 


*User_ind_columns
           it 'll show the columns of indexes by current user but wont show index_owner or Table_owner


 

To find index name:


  SQL> select index_name,index_type,table_name,table_owner from user_indexes where
 table_owner='HR' and table_name='EMPLOYEES';

INDEX_NAME                     INDEX_TYPE                       TABLE_NAME                     TABLE_OWNER
------------------------------ ----------------------------- ------------------------------ ---------------------------


HR_REVERSE_IND                 FUNCTION-BASED NORMAL           EMPLOYEES                           HR
                   

HR_KEY_REV                     NORMAL                          EMPLOYEES                           HR


HR_EMPLOYEES_SAL               NORMAL                          EMPLOYEES                           HR



HR_REV_INDEX                   NORMAL                          EMPLOYEES                           HR


* I am going to take above indexes to monitor "index monitoring"

 

To enable Index Monitor:

SQL> alter index HR_REVERSE_IND monitoring usage;

Index altered.

 

Note:After you enable indexing, you should wait for a while (for example 1-2 days if you think that this index should be used daily), and then you can query v$object_view .

To view V$object_usage result


SQL> select * from V$object_usage where table_name='EMPLOYEES';

INDEX_NAME                     TABLE_NAME                     MON  USE     START_MONITORING    END_MONITORING
------------------- -------------------    ---------------------- -------- ------------------    --------------- ---


HR_REV_INDEX                   EMPLOYEES                      YES  YES  07/29/2017 12:51:59 07/31/2017 15:26:32


HR_REVERSE_IND                 EMPLOYEES                      YES  NO  07/29/2017 12:54:59  07/31/2017 15:26:32

 

To disable index Monitoring:


 SQL>ALTER INDEX HR_REVERSE_IND NOMONITORING USAGE;



 

Note: Disabling monitoring an index, does not change the “used” value on v$object_usage; If you query that view, you should still use the index as "used "between the start_monitoring and end_monitoring.

*The important things V$object_usage will display index Monitoring only the current schema,as a DBA we need to monitor all the schema indexes for this purpose we are going to create view for whole database schema indexes.



CREATE VIEW dba_object_usage (owner,
index_name, table_name, monitoring, used, start_monitoring, end_monitoring )
AS
SELECT do.owner, 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.obj$ io,
SYS.obj$ t,
SYS.ind$ i,
SYS.object_usage ou,
dba_objects do
WHERE i.obj# = ou.obj#
AND io.obj# = ou.obj#
AND t.obj# = i.bo#
AND ou.obj# = do.object_id;


Note:this view will help us to find "Index Monitoring"


 

To view unused index through query of view:


SQL>select * from dba_object_usage where used='NO';

OWNER                          INDEX_NAME           TABLE_NAME                     MON  USE  START_MONITORING    END_MONITORING
------------------------------ --- --- ------------------- -------------------    ------------------------------ ----------------


HR                             COUN_ID_COUNTRIES     COUNTRIES                      YES NO  07/31/2017 12:53:20


SYS                            HR_REVERSE_IND        EMPLOYEES                      YES NO  07/29/2017 12:54:59  07/31/2017 15:26:32

By using start script to enable "index Monitoring"


SET heading off
SET echo off
SET pages 10000
SPOOL start_monitoring.sql
SELECT 'alter index ' || owner || '.' || index_name || ' monitoring usage;'
FROM dba_indexes
WHERE owner NOT IN ('SYS', 'SYSTEM');
SPOOL off
@start1_monitoring.sql

 

By Using stop script to disable "index Monitoring".



   SET heading off
SET echo off
SET pages 10000
SPOOL stop_monitoring.sql
SELECT 'alter index ' || owner || '.' || index_name || ' nomonitoring usage;'
FROM dba_indexes
WHERE owner NOT IN ('SYS', 'SYSTEM');
SPOOL off
@stop_monitoring.sql

  

 

Source: gokhanatil.com,Oracle  Website ,Google