Oracle9i提供了一个新的技术来监控索引以辨别索引有否被使用。

要开始监控一个索引的使用，使用这个命令：

ALTER INDEX index_name MONITORING USAGE;

要停止监控一个索引，输入：

ALTER INDEX index_name NOMONITORING USAGE;

开始监控索引的使用之后，就可以在sys.v$objec_usage视图中查到你所监控的索引的使用情况。

所有被使用过至少一次的索引都可以被监控并显示到这个视图中。不过，一个用户只可以接收它自己schema中的索引使用。Oracle并没有提供一个视图来接收所有模式中的索引。

下面这个脚本提供了以下几个功能：

1. 生成对所有用户schema下的索引使用情况监控的视图

2. 开始对所有用户schema下索引的监控；

3. 停止对所有用户schema下索引的监控；

4. 报告在监控期间未被使用到的索引；

#!/bin/ksh #index_monitor.sh # input parameter: 1: connect str, eg. system/manager@orcl # 2: start|stop if (($#<1)) then echo "Please enter connect str as the first parameter, eg. system/manager@orcl !" exit 0 fi if (($#<2)) then echo "Please enter first|start|stop|report as the second parameter!" exit 0 fi str=$1 case $2 in first) sqlplus '/ as sysdba' << ! 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# / COMMENT ON TABLE SYS.V$ALL_OBJECT_USAGE IS 'Record of all index usage - developed by Daniel Liu' / GRANT SELECT ON SYS.V$ALL_OBJECT_USAGE TO "PUBLIC" / CREATE PUBLIC SYNONYM V$ALL_OBJECT_USAGE FOR SYS.V$ALL_OBJECT_USAGE / ! ;; start) sqlplus $str << ! clear columns breaks set pages 0 feed off wrap off verify off head off trimspool ON colsep ' ' set linesize 100 scan on set term off set pagesize 200 spool tmp.sql select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' MONITORING USAGE;' from dba_indexes where owner not in ('CTXSYS','WMSYS','XDB','MDS YS','ORDSYS','PERFSTAT','QUEST','SYS','SYSTEM','OUTLN','AURORA\$JIS\$UTILITY\$') and index_type <> 'LOB'; spool off exit ! cat tmp.sql | sed -e '/^SQL/d' > start_index_monitoring.sql rm tmp.sql sqlplus $str << ! @./start_index_monitoring.sql exit ! ;; stop) sqlplus $str << ! clear columns breaks set pages 0 feed off wrap off verify off head off trimspool ON colsep ' ' set linesize 100 scan on set term off set pagesize 200 spool tmp.sql select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' NOMONITORING USAGE;' from dba_indexes where owner not in ('CTXSYS','WMSYS','XDB','M DSYS','ORDSYS','PERFSTAT','QUEST','SYS','SYSTEM','OUTLN','AURORA\$JIS\$UTILITY\$') and index_type <> 'LOB'; spool off exit ! cat tmp.sql | sed -e '/^SQL/d' > stop_index_monitoring.sql rm tmp.sql sqlplus $str << ! @./stop_index_monitoring.sql exit ! ;; report) sqlplus $str << ! set feed off set pagesize 200 set linesize 100 ttitle center "Unused Indexes Report" skip 2 spool unused_index.rpt select owner,index_name,table_name,used from v\$all_object_usage where used = 'NO' and owner not in ( 'CTXSYS','WMSYS','XDB','MDSYS','ORDSYS','PERFSTAT','QUEST','SYS','SYSTEM','OUTLN','AURORA\$JIS\$UTILITY\$' ); spool off exit ! ;; *) echo "Please enter start|stop as the second parameter!" ;; esac

脚本用法介绍：

2个输入参数：

第一个为以具有dba权限的用户连接到数据库的字符串，例如：system/manager@oracl

第二个参数说明执行的动作，一共有4个选项

first：第一次使用该脚本，尚未创建SYS.V$ALL_OBJECT_USAGE，需要使用该选项来创建此视图；SYS.V$ALL_OBJECT_USAGE是根据sys.v$object_usage视图来生成的一个视图，比后者多了一个owner的字段；

注意：由于V$ALL_OBJECT_USAGE需要以sys用户登录后执行，所以在使用first选项时，需要在数据库服务器本机运行该脚本；

start：开始对索引使用情况的监控；

stop：停止对索引使用情况的监控；

report：报告未被使用的索引；

使用示例：

./index_monitor.sh system/manager@oracl start