created 10.2010

Script um Index Überwachung einzuschalten:

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
/

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
/