Oracle 9i中監視索引的使用
介紹
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 ) AS select 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_monitoring from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou where 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.sql CREATE OR REPLACE VIEW SYS.V$ALL_OBJECT_USAGE ( OWNER, INDEX_NAME, TABLE_NAME, MONITORING, USED, START_MONITORING, END_MONITORING ) AS select 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_monitoring from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ u where 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_USAGE FOR 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: 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 |
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/35489/viewspace-84680/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 在oracle中監視索引的使用情況Oracle索引
- Oracle9i中監視索引的使用(轉)Oracle索引
- Oracle 索引的使用情況檢視Oracle索引
- 【Oracle】如何查詢檢視時使用索引Oracle索引
- ORACLE 9i statspack使用Oracle
- Oracle索引HINT的使用Oracle索引
- 【索引】oracle查詢使用索引和不使用索引的比較索引Oracle
- Oracle索引的使用規則Oracle索引
- ORACLE 監控索引的使用Oracle索引
- ORACLE 9i資料庫優化案例(2) --- 單列索引變複合索引的優化Oracle資料庫優化索引
- ORACLE 索引使用規Oracle索引
- Oracle 9i輕鬆取得建表和索引的DDL語句(轉)Oracle索引
- Oracle 9i statspack 使用手冊Oracle
- Linux中監視日誌檔案常用的命令!Linux
- ORACLE 9i資料庫優化案例(4) --- 索引改善UPDATEOracle資料庫優化索引
- oracle中監聽器show命令備記Oracle
- oracle 索引使用及索引失效總結Oracle索引
- VB.NET中監視資料夾的變化 (轉)
- oracle中檢視某個表的索引是否有效Oracle索引
- Oracle 9i升級19C 遷移關於失效索引的梳理方法Oracle索引
- oracle order by索引是否使用的情況Oracle索引
- 如何監控oracle的索引是否使用Oracle索引
- Oracle 監控索引的使用率Oracle索引
- 監控Oracle索引的使用情況Oracle索引
- oracle學習筆記——檢視、索引Oracle筆記索引
- Oracle 9i LogMiner工具使用說明Oracle
- 物化檢視上使用bitmap索引索引
- ORACLE 組合索引 使用分析Oracle索引
- 使用index_stats檢視檢視索引效率Index索引
- Oracle 9I 下的AutoTraceOracle
- ORACLE 9I not exists的bugOracle
- DB2檢視索引的使用情況DB2索引
- Oracle 8x中監控sysdba角色使用者登陸情況(轉)Oracle
- Oracle 9i 開啟autotrace on 檢視執行計劃Oracle
- Oracle中組合索引的使用詳解Oracle索引
- oracle的索引Oracle索引
- 在Oracle 9i中Form Builder使用樹心得OracleORMUI
- solaris 安裝 jdk及oracle 9i使用opatchJDKOracle