联系:手机(13429648788) QQ(107644445)
链接:https://www.orasos.com/%e7%9b%91%e6%8e%a7index%e6%b3%a8%e6%84%8f%e4%ba%8b%e9%a1%b9.html
标题:监控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;