索引监控

在某个OLTP系统中某张表上建立了10个索引，由于不确定索引是否有在各程序中被使用，不能随意删除，因此，需要监控已有的索引是否在使用，

官方文档描述：

Monitoring Index Usage

Oracle Database provides a means of monitoring indexes to determine whether they are being used. If an index is not being used, then it can be dropped, eliminating unnecessary statement overhead.

To start monitoring the usage of an index, issue this statement:

ALTER INDEX index MONITORING USAGE;            //开启索引监控
 
Later, issue the following statement to stop the monitoring:

ALTER INDEX index NOMONITORING USAGE;          //关闭索引监控


The view V$OBJECT_USAGE can be queried for the index being monitored to see if the index has been used. The view contains a USED column whose value is YES or NO, depending upon if the index has been used within the time period being monitored. The view also contains the start and stop times of the monitoring period, and a MONITORING column (YES/NO) to indicate if usage monitoring is currently active.

Each time that you specify MONITORING USAGE, the V$OBJECT_USAGE view is reset for the specified index. The previous usage information is cleared or reset, and a new start time is recorded. When you specify NOMONITORING USAGE, no further monitoring is performed, and the end time is recorded for the monitoring period. Until the next ALTER INDEX...MONITORING USAGE statement is issued, the view information is left unchanged.

开启索引监控后，可以从v$object_usage中查询到数据（即使索引还没有被使用），START_MONITORING 字段会写入开始监控的时间；当某条SQL使用了相关的索引以后，其“USED”字段就会更新为’YES‘。监控索引结束后最好关闭监控，以提高效率。

注意：v$object_usage 要去每个USERS 中查询，即使用SYS权限开启的其他USER索引的监控，也无法从SYS用户的V$OBJECT_USAGE 中插到相关的记录。    下面有验证

实验： 测试一个索引

[oracle@tyger ~]$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 28 16:19:01 2014


Copyright (c) 1982, 2005, Oracle.  All rights reserved.




Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


SYS@ORCL>create user tyger identified by tyger;                     //  创建测试用户 tyger


User created.


SYS@ORCL>grant connect,resource to tyger; //   赋予tyger connect，resource 权限


Grant succeeded.


SYS@ORCL>grant select on dba_objects to tyger;                           //   授予 dba_objects 查询权限给 tyger


Grant succeeded.


SYS@ORCL>conn tyger/tyger
Connected.
TYGER@ORCL>create table test_monitor as select * from dba_objects;                        //  创建测试表  


Table created.


TYGER@ORCL