監視未使用索引

　　作者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
From:http://tw.wingwit.com/Article/program/Oracle/201311/17521.html