Dbabc.Net

监控Oracle索引的使用情况

alter index [schema.]index_name monitoring usage;

通过以下sql可以查看索引是否被使用

SELECT index_name, MONITORING, used, start_monitoring, end_monitoring
FROM v$object_usage;

注：由于 V$OBJECT_USAGE 视图限制了只显示当前用户下(io.owner# = USERENV (‘SCHEMAID’))被监控的索引的情况，
因此，通过其他用户登录数据库，将无法看到，如果要查看所有用户下的被监控的索引的情
况，使用如下 SQL: 其实就是把v$object_usage视图中的userenv(‘SCHEMA’)去掉进行修改的。

SELECT u.NAME 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.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#

取消对索引使用情况的监控，使用下列 SQL:

alter index [schema].index_name monitoring usage;

附：创建SYS.v$object_usage视图的脚本

CREATE OR REPLACE FORCE VIEW SYS.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#;
COMMENT ON TABLE SYS.V$OBJECT_USAGE IS ‘Record of index usage’;

COMMENT ON COLUMN SYS.V$OBJECT_USAGE.INDEX_NAME IS ‘Name of the index’;

COMMENT ON COLUMN SYS.V$OBJECT_USAGE.TABLE_NAME IS ‘Name of the table upon which the index was build’;

COMMENT ON COLUMN SYS.V$OBJECT_USAGE.MONITORING IS ‘Whether the monitoring feature is on’;

COMMENT ON COLUMN SYS.V$OBJECT_USAGE.USED IS ‘Whether the index has been accessed’;

COMMENT ON COLUMN SYS.V$OBJECT_USAGE.START_MONITORING IS ‘When the monitoring feature is turned on’;

COMMENT ON COLUMN SYS.V$OBJECT_USAGE.END_MONITORING IS ‘When the monitoring feature is turned off’;

CREATE PUBLIC SYNONYM V$OBJECT_USAGE FOR SYS.V$OBJECT_USAGE;

GRANT SELECT ON SYS.V$OBJECT_USAGE TO PUBLIC;