介紹
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
結論
Oracle9i為監控索引的使用提供了一個新的方法，并且幫助我們辨別未被使用的索引。
這個查找和刪除未被使用索引的能力不但對插入和刪除操作的性能有幫助，而且還節省了存儲空間。在使用索引監控的時候不會看到性能的下降。
