监控index是否被使用

从9i起，ORACLE提供了监控索引是否使用的功能，可以查看v$object_usage来观察索引是否被使用，不过查看这个视图之前需要打开索引的监控功能,使用如下命令可以打开索引监控功能 alter index schema.index_name monitoring usage;使用如下命令关闭索引监控

alter index schema.index_name monitoring usage;

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- ------------------
PK_DEPT DEPT YES NO 12/03/2009 16:10:12
PK_EMP EMP YES YES 12/03/2009 16:10:26

其中use 如果为yes，表示该索引已经使用了，如果为no，表示索引未被使用。

不过这个视图让人郁闷，它只能查看当前用户的索引是否被使用:例如

SQL> show user
USER 为 "SYS"
SQL> select * from v$object_usage;

未选定行

SQL> select * from scott.v$object_usage;
select * from scott.v$object_usage
*
第 1 行出现错误:
ORA-00942: 表或视图不存在

SQL> conn scott/tiger
已连接。
SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- ----------------
PK_DEPT DEPT YES NO 12/03/2009 16:10:12
PK_EMP EMP YES YES 12/03/2009 16:10:26

我们可以创建一个试图，查看所有索引的使用状况

CREATE OR REPLACE VIEW SYS.V$ALL_OBJECT_USAGE
(
OWNER,
INDEX_NAME,
TABLE_NAME,
MONITORING,
USED,
START_MONITORING,
END_MONITORING
)
AS
select u.name, 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, sys.user$ u
where i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
and io.owner# = u.user#;
SQL> CREATE OR REPLACE VIEW SYS.V$ALL_OBJECT_USAGE
2 (
3 OWNER,
4 INDEX_NAME,
5 TABLE_NAME,
6 MONITORING,
7 USED,
8 START_MONITORING,
9 END_MONITORING
10 )
11 AS
12 select u.name, io.name, t.name,
13 decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
14 decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
15 ou.start_monitoring,
16 ou.end_monitoring
17 from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ u
18 where i.obj# = ou.obj#
19 and io.obj# = ou.obj#
20 and t.obj# = i.bo#
21 and io.owner# = u.user#;

视图已创建。

SQL> select * from v$all_object_usage;

OWNER INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ ------------------------------ --- --- ------------------- -------------------
SCOTT PK_DEPT DEPT YES NO 12/03/2009 16:10:12
SCOTT PK_EMP EMP YES YES 12/03/2009 16:10:26

值得注意的是:如果你才执行了alter index index_name monitoring usage; 然后没有用户访问过有该索引的表，那么v$object_usage.use 为no.

SQL> alter index scott.pk_dept monitoring usage;

索引已更改。

SQL> select * from v$all_object_usage;

OWNER INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ ------------------------------ --- --- ------------------- -----------------
SCOTT PK_DEPT DEPT YES NO 12/03/2009 16:58:37
SCOTT PK_EMP EMP YES YES 12/03/2009 16:10:26