Index Monitoring

From Tom's notes
Revision as of 13:50, 31 March 2016 by Tom (talk | contribs) (Created page with "Enable monitoring: <source lang="sql"> select 'alter index ' || owner || '.' || index_name || ' monitoring usage;' from dba_indexes where owner = '?' and index_type not...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Enable monitoring:

select 'alter index ' || owner || '.' || index_name || ' monitoring usage;'
  from dba_indexes
 where owner = '?'
   and index_type not in ('LOB');

Validate it has been enabled on all indexes (in case some were locked):

select index_name
  from user_indexes
 where index_type not in ('LOB')
minus
select index_name
   from v$object_usage;

Verify, make sure to connect as the owner of the objects you are monitoring:

select *
   from v$object_usage;