This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

Monitoring Index Usage In Oracle 10G Using V$OBJECT_USAGE

Have you got some large indexes creeping around?
Some of my systems have 10GB indexes.
These take valuable resources away from the Oracle database:
– DML time (INSERT, UPDATE etc).
– Stats generation time.
– Segment space (hard disk).
– Structure validation time.
– Recovery time.

Removing the index may not be an option.  But what if it’s not actually used!!

How can we tell if it’s used?
Well you could monitor all SQL in the shared pool using the AWR capture script, or you could use Jonathan’s script to see if the index segment(s) has been scanned (full scan).  But these don’t comprehensively give you a definitive answer.
There could be holes in your monitoring.

Instead, you could use the V$OBJECT_USAGE view to monitor index usage.
Although a very basic method, if you only want to know definitively if an index has been used or not, then it gives you the answer.
It is described in great detail here:

Turn on:
alter index <INDEX> monitoring usage;

ALTER INDEX SAPSR3."JCDS~Z01" monitoring usage;

Turn off:
alter index <INDEX> nomonitoring usage;

ALTER INDEX SAPSR3."JCDS~Z01" nomonitoring usage;

Check usage (must be as owner of monitored objects):
select count(1) from v$object_usage;

Or use query below to see all monitored objects:

SELECT owner , index_name , table_name ,
DECODE(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring ,
DECODE(bitand(ou.flags, 1), 0, 'NO', 'YES') used ,
ou.start_monitoring start_monitoring ,
ou.end_monitoring END_MONITORING
sys.user$ u ,
sys.obj$ io ,
sys.obj$ t ,
sys.ind$ i ,
sys.object_usage ou
i.obj# = ou.obj#
AND io.obj# = ou.obj#
AND t.obj# =
AND u.user# = io.owner#;

Add Your Comment

* Indicates Required Field

Your email address will not be published.