Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Thursday, December 20, 2012

11gR2 -Oracle Index usage monitoring


To start monitoring the index usage:

ALTER INDEX MONITORING USAGE;


To stop:

ALTER INDEX MONITORING USAGE;


To monitor the status if you can login as owner then use

select * from v$object_usage;

If viewing the stats as other/super user then use below:

select d.username, io.name INDEX_NAME, t.name TABLE_NAME,
       decode(bitand(i.flags, 65536), 0, 'NO', 'YES') MONITORING,
       decode(bitand(ou.flags, 1), 0, 'NO', 'YES') INDEX_USED,
       ou.start_monitoring,
       ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou,
     dba_users d
where io.owner# = d.user_id
  AND d.username = 'CN'
  and i.obj# = ou.obj#
  and io.obj# = ou.obj#
  and t.obj# = i.bo#;
 
Ex:-



2 comments:

Alberto Farias said...

Correct:

ALTER INDEX NOMONITORING USAGE;

Alberto Farias said...

Correct:

ALTER INDEX NOMONITORING USAGE;