===== Monitor Index Usage =====
**created 10.2010**


Script um Index Überwachung einzuschalten:


<code ql index_usage.sql>
set lines 110 pages 500

col tbln format a30 heading 'Table'
col indx format a30 heading 'Index'
col ityp format a3 heading 'Typ'

variable usn varchar2(20);
exec :usn := '&USER';

break on tblo on tbln

select table_name tbln
     , index_name indx
     , decode(INDEX_TYPE, 'NORMAL','NML','BITMAP','BMP','IOT - TOP','IOT','DOMAIN','DOM','LOB','LOB'
                        , 'FUNCTION-BASED BITMAP','FBB','FUNCTION-BASED NORMAL','FBN'
                        , INDEX_TYPE) ityp
 from all_indexes
where owner=:usn
  and table_owner=:usn
order by table_name
      , index_name
/

spool &&USER._MON_INDEX_ON.sql

select 'alter index '||owner||'.'||index_name||' monitoring usage;'
 from all_indexes
where owner=:usn
  and table_owner=:usn
order by table_name, index_name
/

spool off;


col tbln format a30 hea 'Table'
col indx format a30 hea 'Index'
col mntr format a10 hea 'Monitoring'
col used format a4 hea 'Used'

break on tbln

select  table_name tbln
      , index_name indx
      , monitoring mntr
      , used
from  v$object_usage
order by table_name
      , index_name
/


</code>

Trick: Als Sys User gesamtes Index Monitoring überwachen
<code sql>
select u.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.user$ u
,   sys.obj$ io
,   sys.obj$ t
,   sys.ind$ i
,   sys.object_usage ou
where
i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
and u.user# = io.owner#
order by t.name
/

</code>