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;

http://www.databasejournal.com/features/oracle/index-usage-oracle-database.html
http://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_2077.htm

The system view V$OBJECT_USAGE will only return the rows for the current user.
http://hrivera99.blogspot.com/2011/04/vobjectusage-view-empty.html

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 iu.name as index_owner
     , io.name as index_name
     , tu.name as table_owner
     , yo.name 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
inner
join sys.obj$ io
  on ug.obj# = io.obj#
inner
join sys.user$ iu
  on io.owner# = iu.user#
inner
join sys.ind$ i
  on ug.obj# = i.obj#
inner
join sys.obj$ yo
  on i.bo# = yo.obj#
inner
join sys.user$ tu
  on yo.owner# = tu.user#

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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