熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

監視未使用索引

2013-11-13 15:55:30  來源: Oracle 

  作者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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.