监视未使用索引

　　作者：gototop
　　
　　索引可以加快查询的速度，但索引会占用许多存储空间，在插入和删除行的时候，索引还会引入额外的开销，因此确保索引得到有效利用是我们很关注的一个问题。在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_mon