模板
教程
环境
性能
功能
管理
Oracle 9i新特性研究五(监视未使用索引)
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 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.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' / GRANT SELECT ON SYS.V$ALL_OBJECT_USAGE TO "PUBLIC" / CREATE PUBLIC SYNONYM V$ALL_OBJECT_USAGE FOR SYS.V$ALL_OBJECT_USAGE / 之后就可以从这个视图中查询相关信息了，下面是示例过程： SQL> conn sys/sys as sysdba Connected. SQL> desc v$all_object_useage ERROR: ORA-04043: object v$all_object_useage does not exist SQL> @/oracle/oracle9/cyx/all_object_usage.sql SQL> CREATE OR REPLACE VIEW SYS.V$ALL_OBJECT_USAGE 2 ( 3 OWNER, 4 INDEX_NAME, 5 TABLE_NAME, 6 MONITORING, 7 USED, 8 START_MONITORING, 9 END_MONITORING 10 ) 11 AS 12 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_monitoring 17 from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ u 18 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 IS 2 'Record of all index usage' 3 / Comment created. SQL> GRANT SELECT ON SYS.V$ALL_OBJECT_USAGE TO "PUBLIC" 2 / Grant suclearcase/" target="_blank" >cceeded. SQL> CREATE PUBLIC SYNONYM V$ALL_OBJECT_USAGE 2 FOR SYS.V$ALL_OBJECT_USAGE 3 / Synonym created. SQL> @start_index_monitor.sql SQL> alter index T.INDEX_CFS monitoring usage; Index altered. SQL> alter index T.TEST_PK monitoring usage; Index altered. SQL> conn t/t Connected. SQL> select * from test where id <5; ID NAME ---------- ---------- 1 aaaaaaaa 2 www.ncn.cn 3 aadfaaaa 4 gototop 4 rows selected. SQL> conn sys/sys as sysdba Connected. SQL> @stop_index_monitor.sql SQL> alter index T.INDEX_CFS nomonitoring usage; Index altered. SQL> alter index T.TEST_PK nomonitoring usage; Index altered. SQL> @report_index_usage.sql SQL> set pages 10000 SQL> set pagesize 200 SQL> set linesize 100 SQL> spool report_index_usage.txt SQL> ttitle center "--Report of Unused Indexes--" SQL> select owner,table_name,index_name,used 2 from v$all_object_usage 3 where used='NO'; --Report of Unused Indexes-- OWNER TABLE_NAME INDEX_NAME USED ------------ -------------- ------------ ---- T CFS INDEX_CFS NO 1 row selected. SQL> spool off
