Oracle9i中監視索引的使用(轉)

Oracle9i中監視索引的使用(轉)[@more@]

　　介紹

　　DBA和開發者都喜歡索引。它們可以加速查詢搜尋，特別是在一個資料倉儲的環境中，因為這時資料庫會接收到許多ad-hoc請求。要避免全表搜尋，我們一般在每個可能被搜尋的列中建立索引。不過索引會佔用許多的表空間；在許多的情況下，索引比被索引的表消耗更多的儲存空間。在插入和刪除行的時候，索引還會引入額外的開銷。在Oracle9i之前，要知道一個索引是否被使用是困難的，因此許多資料庫都有許多沒用的索引。這篇文章的目的就是向你介紹透過Oracle9i中的新特性來辨別未使用的索引。

　　辨別未使用的索引

　　Oracle9i提供了一個新的技術來監控索引以辨別索引有否被使用。要開始監控一個索引的使用，使用這個命令：

　　ALTER INDEX index_name MONITORING USAGE;

　　要停止監控一個索引，輸入：

　　ALTER INDEX index_name NOMONITORING 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: 結束監控的時間

　　所有被使用過至少一次的索引都可以被監控並顯示到這個檢視中。不過，一個使用者只可以接收它自己模式中的索引使用。Oracle並沒有提供一個檢視來接收所有模式中的索引。要接收所有模式的索引使用，以SYS使用者登入並且執行以下的指令碼（注意：這並不是Oracle提供的一個指令碼。v$all_object_usage是一個自定義的檢視。它包含多一個列，即索引的擁有者）

　　$ cat all_object_usage.sqlCREATE 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 - developed by Daniel Liu'/GRANT SELECT ON SYS.V$ALL_OBJECT_USAGE TO "PUBLIC"/CREATE PUBLIC SYNONYM V$ALL_OBJECT_USAGEFOR SYS.V$ALL_OBJECT_USAGE/

　　每次你使用MONITORING USAGE，檢視就會為特別的索引而復位。所有以前的使用資訊都會被清除和復位，並且會記錄下一個新的啟動時間。每次你執行NOMONITORING USAGE，就不會進行進一步的監控；監視期間的結束時間就會被記錄下來。如果你刪除一個正在被監控的索引，該索引的相關資訊就會由V$OBJECT_USAGE和V$ALL_OBJECT_USAGE檢視中刪除。

　　辨別資料庫中所有未被使用的索引

　　這個指令碼將會啟動監控所有的索引：

　　##################################################################### ## start_index_monitoring.sh ########################################################################!/bin/ksh# input parameter: 1: password# 2: SIDif (($#<1))thenecho "Please enter 'system' user password as the first parameter !"exit 0fiif (($#<2))thenecho "Please enter instance name as the second parameter!"exit 0fisqlplus -s

　　這個指令碼將會停止監控全部的索引：

　　####################################################################### stop_index_monitoring.sh ########################################################################!/bin/ksh# input parameter: 1: password# 2: SIDif (($#<1))thenecho "Please enter 'system' user password as the first parameter !"exit 0fiif (($#<2))thenecho "Please enter instance name as the second parameter!"exit 0fisqlplus -s

　　這個指令碼將會為所有未被使用的索引產生一個報表：

　　####################################################################### identify_unused_index.sh ########################################################################!/bin/ksh# input parameter: 1: password# 2: SIDif (($#<1))thenecho "Please enter 'system' user password as the first parameter !"exit 0fiif (($#<2))thenecho "Please enter instance name as the second parameter!"exit 0fisqlplus -s

　　以下就是一個未被使用索引報表的例子：

　　Unused Indexes ReportOWNER INDEX_NAME TABLE_NAME USE------------------------------ ------------------------------ ----------------- --- HR DEPT_ID_PK DEPARTMENTS NOHR DEPT_LOCATION_IX DEPARTMENTS NOHR EMP_DEPARTMENT_IX EMPLOYEES NOHR EMP_EMAIL_UK EMPLOYEES NOHR EMP_EMP_ID_PK EMPLOYEES NOHR EMP_JOB_IX EMPLOYEES NOHR EMP_MANAGER_IX EMPLOYEES NOHR EMP_NAME_IX EMPLOYEES NOHR JHIST_DEPARTMENT_IX JOB_HISTORY NOHR JHIST_EMPLOYEE_IX JOB_HISTORY NOHR JHIST_EMP_ID_ST_DATE_PK JOB_HISTORY NOHR JHIST_JOB_IX JOB_HISTORY NOHR JOB_ID_PK JOBS NOHR LOC_CITY_IX LOCATIONS NOHR LOC_COUNTRY_IX LOCATIONS NOHR LOC_ID_PK LOCATIONS NOHR LOC_STATE_PROVINCE_IX LOCATIONS NOHR REG_ID_PK REGIONS NOOE INVENTORY_PK INVENTORIES NOOE INV_PRODUCT_IX INVENTORIES NOOE INV_WAREHOUSE_IX INVENTORIES NOOE ITEM_ORDER_IX ORDER_ITEMS NOOE ITEM_PRODUCT_IX ORDER_ITEMS NOOE ORDER_ITEMS_PK ORDER_ITEMS NOOE ORDER_ITEMS_UK ORDER_ITEMS NOOE ORDER_PK ORDERS NO

　　結論

　　Oracle9i為監控索引的使用提供了一個新的方法，並且幫助我們辨別未被使用的索引。這個查詢和刪除未被使用索引的能力不但對插入和刪除操作的效能有幫助，而且還節省了儲存空間。在使用索引監控的時候不會看到效能的下降。