Oracle9i中監視索引的使用

　　介紹
　　
　　　　DBA和開發者都喜歡索引它們可以加速查詢搜索特別是在一個數據倉庫的環境中因為這時數據庫會接收到許多adhoc請求要避免全表搜索我們一般在每個可能被搜索的列中建立索引不過索引會占用許多的表空間在許多的情況下索引比被索引的表消耗更多的存儲空間在插入和刪除行的時候索引還會引入額外的開銷在Oraclei之前要知道一個索引是否被使用是困難的因此許多數據庫都有許多沒用的索引這篇文章的目的就是向你介紹通過Oraclei中的新特性來辨別未使用的索引
　　
　　　　辨別未使用的索引
　　
　　　　Oraclei提供了一個新的技術來監控索引以辨別索引有否被使用要開始監控一個索引的使用使用這個命令
　　
　　ALTER INDEX index_name MONITORING USAGE;
　　
　　　　要停止監控一個索引輸入
　　
　　ALTER INDEX index_name NOMONITORING 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: 結束監控的時間
　　
　　　　所有被使用過至少一次的索引都可以被監控並顯示到這個視圖中不過一個用戶只可以接收它自己模式中的索引使用Oracle並沒有提供一個視圖來接收所有模式中的索引要接收所有模式的索引使用以SYS用戶登錄並且運行以下的腳本（注意這並不是Oracle提供的一個腳本v$all_object_usage是一個自定義的視圖它包含多一個列即索引的擁有者）
　　
　　$ cat all_object_usagesql
　　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_monitoring
　　ouend_monitoring
　　from sysobj$ io sysobj$ t sysind$ i sysobject_usage ou sysuser$ u
　　where iobj# = ouobj#
　　and ioobj# = ouobj#
　　and tobj# = ibo#
　　and ioowner# = uuser#
　　/
　　COMMENT ON TABLE SYSV$ALL_OBJECT_USAGE IS
　　Record of all index usage developed by Daniel Liu
　　/
　　GRANT SELECT ON SYSV$ALL_OBJECT_USAGE TO PUBLIC
　　/
　　CREATE PUBLIC SYNONYM V$ALL_OBJECT_USAGE
　　FOR SYSV$ALL_OBJECT_USAGE
　　/
　　
　　　　每次你使用MONITORING USAGE視圖就會為特別的索引而復位所有以前的使用信息都會被清除和復位並且會記錄下一個新的啟動時間每次你執行NOMONITORING USAGE就不會進行進一步的監控監視期間的結束時間就會被記錄下來如果你刪除一個正在被監控的索引該索引的相關信息就會由V$OBJECT_USAGE和V$ALL_OBJECT_USAGE視圖中刪除
　　
　　　　辨別數據庫中所有未被使用的索引
　　
　　　　這個腳本將會啟動監控所有的索引
　　
　　##################################################################### ## start_index_monitoringsh ##
　　#####################################################################
　　#!/bin/ksh
　　# input parameter: : password
　　# : SID
　　if (($#<1))
　　then
　　echo "Please enter 'system' user password as the first parameter !"
　　exit 0
　　fi
　　if (($#<2))
　　then
　　echo "Please enter instance name as the second parameter!"
　　exit 0
　　fi
　　sqlplus -s < system/$1@$2
　　set heading off
　　set feed off
　　set pagesize 200
　　set linesize 100
　　spool start_index_monitoring.sql
　　select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' MONITORING USAGE;'
　　from dba_indexes
　　where owner not in ('SYS','SYSTEM','OUTLN','AURORA\$JIS\$UTILITY\$');
　　spool off
　　exit
　　!
　　sqlplus -s < oracle/$1@$2
　　@./start_index_monitoring.sql
　　exit
　　!
　　
　　
　　　　這個腳本將會停止監控全部的索引：
　　
　　#####################################################################
　　## stop_index_monitoring.sh ##
　　#####################################################################
　　#!/bin/ksh
　　# input parameter: 1: password
　　# 2: SID
　　if (($#<1))
　　then
　　echo "Please enter 'system' user password as the first parameter !"
　　exit 0
　　fi
　　if (($#<2))
　　then
　　echo "Please enter instance name as the second parameter!"
　　exit 0
　　fi
　　sqlplus -s < system/$1@$2
　　set heading off
　　set feed off
　　set pagesize 200
　　set linesize 100
　　spool stop_index_monitoring.sql
　　select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' NOMONITORING USAGE;'
　　from dba_indexes
　　where owner not in ('SYS','SYSTEM','OUTLN','AURORA/$JIS/$UTILITY/$');
　　spool off
　　exit
　　!
　　exit
　　sqlplus -s < oracle/$1@$2
　　@./stop_index_monitoring.sql
　　exit
　　!
　　
　　　　這個腳本將會為所有未被使用的索引產生一個報表：
　　
　　#####################################################################
　　## identify_unused_index.sh ##
　　#####################################################################
　　#!/bin/ksh
　　# input parameter: 1: password
　　# 2: SID
　　if (($#<1))
　　then
　　echo "Please enter 'system' user password as the first parameter !"
　　exit 0
　　fi
　　if (($#<2))
　　then
　　echo "Please enter instance name as the second parameter!"
　　exit 0
　　fi
　　sqlplus -s < system/$1@$2
　　set feed off
　　set pagesize 200
　　set linesize 100
　　ttitle center "Unused Indexes Report" skip 2
　　spool unused_index.rpt
　　select owner,index_name,table_name,used
　　from v\$all_object_usage
　　where used = 'NO';
　　spool off
　　exit
　　!
　　
　　　　以下就是一個未被使用索引報表的例子：
　　
　　Unused Indexes Report
　　
　　OWNER INDEX_NAME TABLE_NAME USE
　　------------------------------ ------------------------------ ----------------- --- HR DEPT_ID_PK DEPARTMENTS NO
　　HR DEPT_LOCATION_IX DEPARTMENTS NO
　　HR EMP_DEPARTMENT_IX EMPLOYEES NO
　　HR EMP_EMAIL_UK EMPLOYEES NO
　　HR EMP_EMP_ID_PK EMPLOYEES NO
　　HR EMP_JOB_IX EMPLOYEES NO
　　HR EMP_MANAGER_IX EMPLOYEES NO
　　HR EMP_NAME_IX EMPLOYEES NO
　　HR JHIST_DEPARTMENT_IX JOB_HISTORY NO
　　HR JHIST_EMPLOYEE_IX JOB_HISTORY NO
　　HR JHIST_EMP_ID_ST_DATE_PK JOB_HISTORY NO
　　HR JHIST_JOB_IX JOB_HISTORY NO
　　HR JOB_ID_PK JOBS NO
　　HR LOC_CITY_IX LOCATIONS NO
　　HR LOC_COUNTRY_IX LOCATIONS NO
　　HR LOC_ID_PK LOCATIONS NO
　　HR LOC_STATE_PROVINCE_IX LOCATIONS NO
　　HR REG_ID_PK REGIONS NO
　　OE INVENTORY_PK INVENTORIES NO
　　OE INV_PRODUCT_IX INVENTORIES NO
　　OE INV_WAREHOUSE_IX INVENTORIES NO
　　OE ITEM_ORDER_IX ORDER_ITEMS NO
　　OE ITEM_PRODUCT_IX ORDER_ITEMS NO
　　OE ORDER_ITEMS_PK ORDER_ITEMS NO
　　OE ORDER_ITEMS_UK ORDER_ITEMS NO
　　OE ORDER_PK ORDERS NO
　　

From:http://tw.wingwit.com/Article/program/Oracle/201311/18193.html