Oracle Index Usage

Unlike SQL Server, Oracle dose not collect a lot of statistics on index usage. One way of collecting the index usage is turn on object monitoring and collect the results in V$OBJECT_USAGE system view.

alter index {index name} monitoring usage;
alter index {index name} nomonitoring usage;

The system view V$OBJECT_USAGE will only return the rows for the current user.

This is the list of monitoring indexes.

-- indexes are being monitored
select * 
from sys.ind$
where bitand(flags, 65536) = 65536

This is the replacement for the V$OBJECT_USAGE.

select as index_owner
     , as index_name
     , as table_owner
     , as table_name
     , decode(bitand(i.flags, 65536), 0, 'NO', 'YES') as monitoring
     , decode(bitand(ug.flags, 1), 0, 'NO', 'YES') as usage
     , ug.start_monitoring
     , ug.end_monitoring
from sys.object_usage ug
join sys.obj$ io
  on ug.obj# = io.obj#
join sys.user$ iu
  on io.owner# = iu.user#
join sys.ind$ i
  on ug.obj# = i.obj#
join sys.obj$ yo
  on = yo.obj#
join sys.user$ tu
  on yo.owner# = tu.user#


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s