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
