索引可以加快查询的速度，但索引会占用许多存储空间，在插入和删除行的时候，索引还会引入额外的开销，因此确保索引得到有效利用是我们很关注的一个问题。在Oracle9i之前，要知道一个索引是否被使用是困难的，而Oracle 9i中提供了一个有效的监控方法:ALTER INDEX MONITORING USAGE。下面我讲详细说明如何使用该方法来鉴别未使用的索引。
一、我们先通过一个例子具体说明“ALTER INDEX MONITORING USAGE”的使用方法：
1、建测试表
create table test(id number(3),name varchar2(10));
insert into test values(1,'aaaaaaaa');
insert into test values(2,'www.ncn.cn');
insert into test values(3,'aadfaaaa');
insert into test values(4,'gototop');
insert into test values(5,'shenzhen');
insert into test values(6,'china');
commit;
alter table test add (constraint test_pk primary key (id));
2、查询v$object_usage(因为没有监视，所以还看不到内容)
column index_name format a12
column monitoring format a10
column used format a4
column start_monitoring format a19
column end_monitoring format a19
select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;
SQL l
1* select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage
SQL /
no rows selected
Elapsed: 00:00:00.00
3、开始监控索引的使用情况
SQL alter index test_pk monitoring usage;
Index altered.
Elapsed: 00:00:00.05
4、查询v$object_usage(可以看到正监视中)
SQL select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;
INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
TEST_PK YES NO 05/15/2003 13:28:22
Elapsed: 00:00:00.00
5、使用索引进行查询
SQL set autotrace on explain
SQL select * from test where id = 2;
ID NAME
---------- ----------
2 www.ncn.cn
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (UNIQUE SCAN) OF 'TEST_PK' (UNIQUE)
SQL set autotrace off
SQL /
ID NAME
---------- ----------
2 www.ncn.cn
Elapsed: 00:00:00.00
SQL
从上我们可以看到确实使用了索引。
6、查询v$object_usage(可以看到索引被使用过，但目前还处于被监视过程中)
SQL select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;
INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
TEST_PK YES YES 05/15/2003 13:28:22
Elapsed: 00:00:00.00
7、停止监视，并查询v$object_usage
SQL alter index test_pk nomonitoring usage;
Index altered.
Elapsed: 00:00:05.03
SQL select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;
INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
TEST_PK NO YES 05/15/2003 13:28:22 05/15/2003 13:40:00
Elapsed: 00:00:00.64
到此为止，监视结束，MONITORING为NO，END_MONITORING给出了时间戳。
二、v$object_usage视图解释
从上面的例子中我们可以看出，索引的监视信息都是存在在v$objec_usage视图中，该视图的定义如下：
CREATE OR REPLACE 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'
/
GRANT SELECT ON SYS.V$OBJECT_USAGE TO "PUBLIC"
/
下面是该视图列的描述：
INDEX_NAME: sys.obj$.name 中的索引名字
TABLE_NAME: sys.obj$obj$name 中的表名
MONITORING: YES (索引正在被监控), NO (索引没有被监控)
USED: YES (索引已经被使用过), NO (索引没有被使用过)
START_MONITORING: 开始监控的时间
END_MONITORING: 结束监控的时间
所有被使用过至少一次的索引都可以被监控并显示到这个视图中。
三、监视数据库中所有索引的使用情况
1、生成开始/结束监视索引的SQL脚本：
set heading off
set echo off
set feedback off
set pages 10000
spool start_index_monitor.sql
select 'alter index '||owner||'.'||index_name||' monitoring usage;'
from dba_indexes
where owner in ('YOUR','PROD_DB','OWNER','LIST');
spool off
set heading on
set echo on
set feedback on
------------------------------------------------
set heading off
set echo off
set feedback off
set pages 10000
spool stop_index_monitor.sql
select 'alter index '||owner||'.'||index_name||' nomonitoring usage;'
from dba_indexes
where owner in ('YOUR','PROD_DB','OWNER','LIST');
spool off
set heading on
set echo on
set feedback on
2、进行监视并查询结果：
在业务量比较多的一天上班时运行start_index_monitor.sql，下班前运行stop_index_monitor.sql，之后就可以在各用户自己的v$object_usage视图中看到该SCHEMA下的索引使用情况了：
SQL conn t/t
Connected.
SQL select index_name,table_name,used
2 from v$object_usage
3 where used='NO';
INDEX_NAME TABLE_NAME USED
------------ ------------------------------ ----
TEST_PK TEST NO
1 row selected.
SQL
3、改进结果查寻方法
你也许已经注意到，上面查询结果是需要我们单独查询各SCHEMA中的v$object_usage，其实我们可以通过给v$object_usage视图添加一个owner列来创建一个可以存储所有SHCEMA的v$object_usage视图，不妨叫做v$all_object_usage，定义如下：
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.en
