监控Index注意事项

1、监控语句
ALTER INDEX owner.index_name MONITORING USAGE;

2、查看监控情况
select * from v$object_usage;

3、使用sys用户无法查询到监控对象问题解决
3.1）查询建立v$object_usage的语句

CREATE OR REPLACE FORCE VIEW v$object_usage (index_name,
table_name,
MONITORING,
used,
start_monitoring,
end_monitoring
)
AS
SELECT io.NAME, t.NAME, DECODE (BITAND (i.flags, 65536), 0, 'NO', 'YES'),
DECODE (BITAND (ou.flags, 1), 0, 'NO', 'YES'), ou.start_monitoring,
ou.end_monitoring
FROM SYS.obj$ io, SYS.obj$ t, SYS.ind$ i, SYS.object_usage ou
WHERE io.owner# = USERENV ('SCHEMAID')
AND i.obj# = ou.obj#
AND io.obj# = ou.obj#
AND t.obj# = i.bo#;

3.2）从该语句中发现，只有当前用户才可以查询到index的监控情况，即使sys也不能查看到

3.3）解决sys可以查询到index监控情况可以除掉io.owner# = USERENV (‘SCHEMAID’)即可

SELECT *
  FROM (SELECT USERNAME OWNER,
               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') USED,
               OU.START_MONITORING START_MONITORING,
               OU.END_MONITORING END_MONITORING
          FROM SYS.OBJ$         IO,
               SYS.OBJ$         T,
               SYS.IND$         I,
               SYS.OBJECT_USAGE OU,
               DBA_USERS        U
         WHERE I.OBJ# = OU.OBJ#
           AND IO.OBJ# = OU.OBJ#
           AND T.OBJ# = I.BO#
           AND U.USER_ID = IO.OWNER#)
--USED='YES'--表示有使用
--USED='NO'--表示没使用
 WHERE USED = 'YES';

如果想查询方便，可以创建sys下面的下个视图

4、停止监控
ALTER INDEX owner.index_name NOMONITORING USAGE;