===== Monitor Index Usage =====
**created 10.2010**
Script um Index Überwachung einzuschalten:
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
/
Trick: Als Sys User gesamtes Index Monitoring überwachen
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
/