Oracle 9i新特性研究 五 监视未使用索引 索引可以加快查询的速度，但索引会占用许多存储空间，在插入和删除行的时候，索引还会引入额外的开销，因此确保索引得到有效利用是我们很关注的一个问题。在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 a12column monitoring format a10column used format a4column start_monitoring format a19column end_monitoring format a19select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;SQL> l1* select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usageSQL> /no rows selectedElapsed: 00:00:00.003、开始监控索引的使用情况SQL> alter index test_pk monitoring usage;Index altered.Elapsed: 00:00:00.054、查询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:22Elapsed: 00:00:00.005、使用索引进行查询SQL> set autotrace on eXPlainSQL> select * from test where id = 2;ID NAME---------- ----------2 www.ncn.cnElapsed: 00:00:00.00Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE access (BY INDEX ROWID) OF 'TEST'2 1 INDEX (UNIQUE SCAN) OF 'TEST_PK' (UNIQUE)SQL> set autotrace offSQL> /ID NAME---------- ----------2 www.ncn.cnElapsed: 00:00:00.00SQL>从上我们可以看到确实使用了索引。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:22Elapsed: 00:00:00.007、停止监视，并查询v$object_usageSQL> alter index test_pk nomonitoring usage;Index altered.Elapsed: 00:00:05.03SQL> 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:00Elapsed: 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)ASselect 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_monitoringfrom sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ouwhere 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 offset echo offset feedback offset pages 10000spool start_index_monitor.sqlselect 'alter index 'owner'.'index_name' monitoring usage;'from dba_indexeswhere owner in ('YOUR','PROD_DB','OWNER','LIST');spool offset heading onset echo onset feedback on------------------------------------------------set heading offset echo offset feedback offset pages 10000spool stop_index_monitor.sqlselect 'alter index 'owner'.'index_name' nomonitoring usage;'from dba_indexeswhere owner in ('YOUR','PROD_DB','OWNER','LIST');spool offset heading onset echo onset feedback on2、进行监视并查询结果： 在业务量比较多的一天上班时运行start_index_monitor.sql，下班前运行stop_index_monitor.sql，之后就可以在各用户自己的v$object_usage视图中看到该SCHEMA下的索引使用情况了：SQL> conn t/tConnected.SQL> select index_name,table_name,used
2 from v$object_usage3 where used='NO';INDEX_NAME TABLE_NAME USED------------ ------------------------------ ----TEST_PK TEST NO1 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)ASselect 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_monitoringfrom sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ uwhere 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'/GRANT SELECT ON SYS.V$ALL_OBJECT_USAGE TO "PUBLIC"/CREATE PUBLIC SYNONYM V$ALL_OBJECT_USAGEFOR SYS.V$ALL_OBJECT_USAGE/之后就可以从这个视图中查询相关信息了，下面是示例过程：SQL> conn sys/sys as sysdbaConnected.SQL> desc v$all_object_useageERROR:ORA-04043: object v$all_object_useage does not existSQL> @/oracle/oracle9/cyx/all_object_usage.sqlSQL> CREATE OR REPLACE VIEW SYS.V$ALL_OBJECT_USAGE2 (3 OWNER,4 INDEX_NAME,5 TABLE_NAME,6 MONITORING,7 USED,8 START_MONITORING,9 END_MONITORING10 )11 AS12 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_monitoring17 from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ u18 where i.obj# = ou.obj#19 and io.obj# = ou.obj#20 and t.obj# = i.bo#21 and io.owner# = u.user#22 /View created.SQL> COMMENT ON TABLE SYS.V$ALL_OBJECT_USAGE IS2 'Record of all index usage'3 /Comment created.SQL> GRANT SELECT ON SYS.V$ALL_OBJECT_USAGE TO "PUBLIC"2 /Grant sUCceeded.SQL> CREATE PUBLIC SYNONYM V$ALL_OBJECT_USAGE2 FOR SYS.V$ALL_OBJECT_USAGE3 /Synonym created.SQL> @start_index_monitor.sqlSQL> alter index T.INDEX_CFS monitoring usage;Index altered.SQL> alter index T.TEST_PK monitoring usage;Index altered.SQL> conn t/tConnected.SQL> select * from test where id <5;
ID NAME---------- ----------1 aaaaaaaa2 www.ncn.cn3 aadfaaaa4 gototop4 rows selected.SQL> conn sys/sys as sysdbaConnected.SQL> @stop_index_monitor.sqlSQL> alter index T.INDEX_CFS nomonitoring usage;Index altered.SQL> alter index T.TEST_PK nomonitoring usage;Index altered.SQL> @report_index_usage.sqlSQL> set pages 10000SQL> set pagesize 200SQL> set linesize 100SQL> spool report_index_usage.txtSQL> ttitle center "--Report of Unused Indexes--"SQL> select owner,table_name,index_name,used2 from v$all_object_usage3 where used='NO';--Report of Unused Indexes--OWNER TABLE_NAME INDEX_NAME USED------------ -------------- ------------ ----T CFS INDEX_CFS NO1 row selected.SQL> spool off
