Oracle Index Monitoring Steps

If you are maintaining a large database that contains thousands of indexes. As part of your proactive maintenance, you want to determine if any indexes are not being used. You realize that unused indexes have a detrimental impact on performance, because every time a row is inserted, updated, and deleted, the corresponding index has to be maintained. This consumes CPU resources and disk space. If an index isn’t being used, it should be dropped.

Use the ALTER INDEX…MONITORING USAGE statement to enable basic index monitoring. The following example enables index monitoring on an index named F_REGS_IDX1:

SQL> alter index f_regs_idx1 monitoring usage;

The first time the index is accessed, Oracle records this; you can view whether an index has been accessed via the V$OBJECT_USAGE view. To report which indexes are being monitored and have ever been used, run this query:

SQL> select index_name, table_name, monitoring, used from v$object_usage;

If the index has ever been used in a SELECT statement, then the USED column will contain the YES value. Here is some sample output from the prior query:

INDEX_NAME TABLE_NAME MON USED

------------------------------ ------------------------------ --- ----

F_REGS_IDX1 F_REGS YES YES

Most likely, you won’t monitor only one index. Rather, you’ll want to monitor all indexes for a user. In this situation, use SQL to generate SQL to create a script you can run to turn on monitoring for all indexes. Here’s such a script:

set pagesize 0 head off linesize 132

spool enable_mon.sql

select 'alter index ' || index_name || ' monitoring usage;' from user_indexes;

spool off;

To disable monitoring on an index, use the NOMONITORING USAGE clause—for example:

SQL> alter index f_regs_idx1 nomonitoring usage;

The main advantage to monitoring index usage is to identify indexes not being used. This allows you to identify indexes that can be dropped. This will free up disk space and improve the performance of DML statements.

The V$OBJECT_USAGE view shows information only for the currently connected user. You can verify this behavior by inspecting the TEXT column of DBA_VIEWS for the V$OBJECT_USAGE definition:

SQL> select text from dba_views where view_name = 'V$OBJECT_USAGE';

If you’re logged in as a DBA privileged user and want to view the status of all indexes that have monitoring enabled (regardless of the user), execute this query:

select 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 where i.obj# = ou.obj# and io.obj# = ou.obj# and t.obj# = i.bo#;

The prior query removes the line from the query that restricts output to display information only for the currently logged-in user. This provides you with a convenient way to view all monitored indexes.

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.