index usage monitoring / invisible indexes

In an application lifecycle some indexes will be added to a table, again and again.

But after a while nobody knows which index is really used or not.

Just delete the index ? hmmm who knows 😉

A simple option is to use index monitoring.

alter index <index_name> monitoring usage;

Now you can see in v$object_usage whether the index has been used since activating the monitoring or not

select * from v$object_usage;

BUT ! the name of the view is a little bit confusing – It’s not like a common v$-view – it’s only visible to the specific schema owner.

e.g. when activating the monitoring usage in schema RGRAEFF only RGRAEFF can see the content.

If sys is quering the view “no rows selected” ….

To give you – as a dba – the possibility to see the usage create the following view:

create or replace view V$ALL_OBJECT_USAGE_RGRAEFF
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 u.user# = io.owner#
   and t.obj# = i.bo#
   and io.obj# = ou.obj#  
/

Give the view a special name, so Now you can see ALL monitored indexes.

select * from v$all_object_usage_rgraeff
where used = 'NO'
order by owner, table_name;

To disable index monitoring usage:

alter index <index_name> nomonitoring usage;

It’s almost to complex to monitor each single index. So just build a spool-file to monitor all indexes of a specific schema:

set pages 0
set lines 120
set serveroutput off
spool enable_index_monitoring.sql
select 'alter index '||owner||'.'||index_name||' monitoring usage;' 
 from dba_indexes
 where owner in ('RGRAEFF','SCOTT');
spool off;

and run afterwards

@enable_index_monitoring.sql

There is no performance impact on index monitoring.

If you alter an index monitoring and the index is already in monitoring the recently gathered informations are gone – this is equal to a monitoring reset.

 

making an index INVISIBLE

since 11g a new feature invisible indexes is availible.

The intention is to create indexes for test purposes without impacting the “normal” production environment!

By default the init.ora settings ignore invisible indexes (optimizer_use_invisible_indexes=false).

When you’re not sure a new index will help you or not, just create an invisible index and set the session to use those invisible indexes.

e.g.

create index i_2 on emp(empno) invisible;

Now – the index will be created  – a real physical index which is also maintained (DML).

to see whether the index might help you, activate the usage of invisibile indexes

alter session set optimizer_use_invisible_indexes=true;

make your tests (autotrace/ dbms_xplan, 10046, 10053…..)

 

If you want to publish the index

alter index i_2 visible;

now all sessions will anticipate of this index!!!

 

to get back to remove unused indexes 🙂

an other option before droping an index would be to make an existing (probably unused ) index INVISIBLE.

Maybe dropping and rebuilding an index takes too long..

alter index <indexname> invisible;

keep in mind – the index will also be updated by dml operations as before!

 

If  you can be sure the invisible index can be dropped after a while….