|
作者gototop 索引可以加快查询的速度但索引会占用许多存储空间在插入和删除行的时候索引还会引入额外的开销因此确保索引得到有效利用是我们很关注的一个问题在Oraclei之前要知道一个索引是否被使用是困难的而Oracle i中提供了一个有效的监控方法:ALTER INDEX MONITORING USAGE下面我讲详细说明如何使用该方法来鑒别未使用的索引 一我们先通过一个例子具体说明ALTER INDEX MONITORING USAGE的使用方法 建测试表 create table test(id number()name varchar()); insert into test values(aaaaaaaa); insert into test values(); insert into test values(aadfaaaa); insert into test values(gototop); insert into test values(shenzhen); insert into test values(china); commit; alter table test add (constraint test_pk primary key (id)); 查询v$object_usage(因为没有监视所以还看不到内容) column index_name format a column monitoring format a column used format a column start_monitoring format a column end_monitoring format a select index_namemonitoringusedstart_monitoringend_monitoring from v$object_usage; SQL> l * select index_namemonitoringusedstart_monitoringend_monitoring from v$object_usage SQL> / no rows selected Elapsed: :: 开始监控索引的使用情况 SQL> alter index test_pk monitoring usage; Index altered Elapsed: :: 查询v$object_usage(可以看到正监视中) SQL> select index_namemonitoringusedstart_monitoringend_monitoring from v$object_usage; INDEX_NAME MONITORING USED START_MONITORING END_MONITORING TEST_PK YES NO // :: Elapsed: :: 使用索引进行查询 SQL> set autotrace on explain SQL> select * from test where id = ; ID NAME Elapsed: :: Execution Plan
SELECT STATEMENT Optimizer=CHOOSE TABLE ACCESS (BY INDEX ROWID) OF TEST INDEX (UNIQUE SCAN) OF TEST_PK (UNIQUE) SQL> set autotrace off SQL> / ID NAME Elapsed: :: SQL> 从上我们可以看到确实使用了索引 查询v$object_usage(可以看到索引被使用过但目前还处于被监视过程中) SQL> select index_namemonitoringusedstart_monitoringend_monitoring from v$object_usage; INDEX_NAME MONITORING USED START_MONITORING END_MONITORING TEST_PK YES YES // :: Elapsed: :: 停止监视并查询v$object_usage SQL> alter index test_pk nomonitoring usage; Index altered Elapsed: :: SQL> select index_namemonitoringusedstart_monitoringend_monitoring from v$object_usage; INDEX_NAME MONITORING USED START_MONITORING END_MONITORING TEST_PK NO YES // :: // :: Elapsed: :: 到此为止监视结束MONITORING为NOEND_MONITORING给出了时间戳 二v$object_usage视图解释 从上面的例子中我们可以看出索引的监视信息都是存在在v$objec_usage视图中该视图的定义如下 CREATE OR REPLACE VIEW SYSV$OBJECT_USAGE ( INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING ) AS select ioname tname decode(bitand(iflags ) NO YES) decode(bitand(ouflags ) NO YES) oustart_monitoring ouend_monitoring from sysobj$ io sysobj$ t sysind$ i sysobject_usage ou where ioowner# = userenv(SCHEMAID) and iobj# = ouobj# and ioobj# = ouobj# and tobj# = ibo# / COMMENT ON TABLE SYSV$OBJECT_USAGE IS Record of index usage / GRANT SELECT ON SYSV$OBJECT_USAGE TO PUBLIC / 下面是该视图列的描述 INDEX_NAME: sysobj$name 中的索引名字 TABLE_NAME: sysobj$obj$name 中的表名 MONITORING: YES (索引正在被监控) NO (索引没有被监控) USED: YES (索引已经被使用过) NO (索引没有被使用过) START_MONITORING: 开始监控的时间 END_MONITORING: 结束监控的时间 所有被使用过至少一次的索引都可以被监控并显示到这个视图中 三监视数据库中所有索引的使用情况 生成开始/结束监视索引的SQL脚本 set heading off set echo off set feedback off set pages spool start_index_monitorsql select alter index ||owner||||index_name|| monitoring usage; from dba_indexes where owner in (YOURPROD_DBOWNERLIST); spool off set heading on set echo on set feedback on
set heading off set echo off set feedback off set pages spool stop_index_monitorsql select alter index ||owner||||index_name|| nomonitoring usage; from dba_indexes where owner in (YOURPROD_DBOWNERLIST); spool off set heading on set echo on set feedback on 进行监视并查询结果 在业务量比较多的一天上班时运行start_index_monitorsql下班前运行stop_index_monitorsql之后就可以在各用户自己的v$object_usage视图中看到该SCHEMA下的索引使用情况了 SQL> conn t/t Connected SQL> select index_nametable_nameused from v$object_usage where used=NO; INDEX_NAME TABLE_NAME USED TEST_PK TEST NO row selected SQL> 改进结果查寻方法 你也许已经注意到上面查询结果是需要我们单独查询各SCHEMA中的v$object_usage其实我们可以通过给v$object_usage视图添加一个owner列来创建一个可以存储所有SHCEMA的v$object_usage视图不妨叫做v$all_object_usage定义如下 CREATE OR REPLACE VIEW SYSV$ALL_OBJECT_USAGE ( OWNER INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING ) AS select uname ioname tname decode(bitand(iflags ) NO YES) decode(bitand(ouflags ) NO YES) oustart_mon |
