V$OBJECT_MONITORING gotcha...

I have been checking out some usage patterns on a database I am migrating to Exadata. I won't go into the 'drop all of the indexes' herd mentality... but I would like to know what is really being used on this system so we can make some educated guesses about what is really needed. Luckily it is a DW / ETL type of application that has a comprehensive nightly refresh. Yes, a nice way of saying flush and fill. Well at least I know they touch _every_ important object _every_ night.

So I perused the ALTER INDEX MONITORING USAGE documentation. Sounds great. There are lots of tutorials out there, Google for INDEX MONITORING ORACLE and some fine instructions will pop up to the top of your search results.

I'll cut to the chase - here's the gotcha... and not all the tutorials make this clear... V$OBJECT_USAGE only returns data for the currently logged in user. If you are used to being SYS or SYSTEM or DBA Role then this view will often come up empty. I didn't turn on monitoring for the account I was logged in as... I turned it on for the app schemas I was interested in!

Here is a rework of V$OBJECT_USAGE which will give you monitoring info for all indexes that have usage info available:

select us.name OWNER,
       t.name TABLE_NAME,
       io.name INDEX_NAME, 
       decode(bitand(i.flags, 65536), 0, 'NO', 'YES') MONITORING,
       decode(bitand(ou.flags, 1), 0, 'NO', 'YES') USED,
       ou.start_monitoring,
       ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ us
where i.obj# = ou.obj#
  and io.obj# = ou.obj#
  and t.obj# = i.bo#
  and us.user#=io.owner#
ORDER BY OWNER, TABLE_NAME, INDEX_NAME;

-Dave