监控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