介绍

　　DBA和开发者都喜欢索引。它们可以加速查询搜索，特别是在一个数据仓库的环境中，因为这时数据库会接收到许多ad-hoc请求。要避免全表搜索，我们一般在每个可能被搜索的列中建立索引。不过索引会占用许多的表空间；在许多的情况下，索引比被索引的表消耗更多的存储空间。在插入和删除行的时候，索引还会引入额外的开销。在Oracle9i之前，要知道一个索引是否被使用是困难的，因此许多数据库都有许多没用的索引。这篇文章的目的就是向你介绍通过Oracle9i中的新特性来辨别未使用的索引。

　　辨别未使用的索引

　　Oracle9i提供了一个新的技术来监控索引以辨别索引有否被使用。要开始监控一个索引的使用，使用这个命令：

ALTERINDEXindex_nameMONITORINGUSAGE;
　　要停止监控一个索引，输入： 财管家,园,fs119.net

ALTERINDEXindex_nameNOMONITORINGUSAGE;
　　在v$objec_usage视图中包含有索引监控的使用信息。

CREATEORREPLACEVIEWSYS.V$OBJECT_USAGE
(
INDEX_NAME,
TABLE_NAME,
MONITORING,
USED,
START_MONITORING,
END_MONITORING
)
AS
selectio.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
fromsys.obj$io,sys.obj$t,sys.ind$i,sys.object_usageou
whereio.owner#=userenv('SCHEMAID')
andi.obj#=ou.obj#
andio.obj#=ou.obj#
andt.obj#=i.bo#
/
COMMENTONTABLESYS.V$OBJECT_USAGEIS
'Recordofindexusage'
/
GRANTSELECTONSYS.V$OBJECT_USAGETO"PUBLIC"
/
　　该视图显示了由数据库中收集来的索引使用统计。以下就是该视图中的列的描述：

　　　INDEX_NAME:sys.obj$.name中的索引名字 财管家园,fs119.net

　　　TABLE_NAME:sys.obj$obj$name中的表名

　　　MONITORING:YES(索引正在被监控),NO(索引没有被监控)

　　　USED:YES(索引已经被使用过),NO(索引没有被使用过)

　　　START_MONITORING:开始监控的时间

　　　END_MONITORING:结束监控的时间

　　所有被使用过至少一次的索引都可以被监控并显示到这个视图中。不过，一个用户只可以接收它自己模式中的索引使用。Oracle并没有提供一个视图来接收所有模式中的索引。要接收所有模式的索引使用，以SYS用户登录并且运行以下的脚本（注意：这并不是Oracle提供的一个脚本。v$all_object_usage是一个自定义的视图。它包含多一个列，即索引的拥有者）

$catall_object_usage.sql
CREATEORREPLACEVIEWSYS.V$ALL_OBJECT_USAGE
(
OWNER,
INDEX_NAME,
TABLE_NAME,
MONITORING,
USED,
START_MONITORING,

财软联.盟.fs119.net

END_MONITORING
)
AS
selectu.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
fromsys.obj$io,sys.obj$t,sys.ind$i,sys.object_usageou,sys.user$u
wherei.obj#=ou.obj#
andio.obj#=ou.obj#
andt.obj#=i.bo#
andio.owner#=u.user#
/
COMMENTONTABLESYS.V$ALL_OBJECT_USAGEIS
'Recordofallindexusage-developedbyDanielLiu'
/
GRANTSELECTONSYS.V$ALL_OBJECT_USAGETO"PUBLIC"
/
CREATEPUBLICSYNONYMV$ALL_OBJECT_USAGE
FORSYS.V$ALL_OBJECT_USAGE
/

　　每次你使用MONITORINGUSAGE，视图就会为特别的索引而复位。所有以前的使用信息都会被清除和复位，并且会记录下一个新的启动时间。每次你执行NOMONITORINGUSAGE，就不会进行进一步的监控；监视期间的结束时间就会被记录下来。如果你删除一个正在被监控的索引，该索引的相关信息就会由V$OBJECT_USAGE和V$ALL_OBJECT_USAGE视图中删除。 财管 家园 fs119.net

财 管家园 fs119.net

财软联盟.fs119.net

财管家园.fs119.net

财,管家园,fs119.net

财管家 园 fs119.net

　　辨别数据库中所有未被使用的索引

　　这个脚本将会启动监控所有的索引：

财软联盟.fs119.net

#######################################################################start_index_monitoring.sh##
#####################################################################
#!/bin/ksh
#inputparameter:1:password
#2:SID
if(($#<1))
then
echo"Pleaseenter'system'userpasswordasthefirstparameter!"
exit0
fi
if(($#<2))
then
echo"Pleaseenterinstancenameasthesecondparameter!"
exit0
fi
sqlplus-s<system/$1@$2
setheadingoff
setfeedoff
setpagesize200
setlinesize100
spoolstart_index_monitoring.sql
select'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'MONITORINGUSAGE;'
fromdba_indexes
whereownernotin('SYS','SYSTEM','OUTLN','AURORA\$JIS\$UTILITY\$');
spooloff
exit
!
sqlplus-s<oracle/$1@$2
@./start_index_monitoring.sql
exit
!

　　这个脚本将会停止监控全部的索引：

##################################################################### 财管家园 fs119.net
##stop_index_monitoring.sh##
#####################################################################
#!/bin/ksh
#inputparameter:1:password
#2:SID
if(($#<1))
then
echo"Pleaseenter'system'userpasswordasthefirstparameter!"
exit0
fi
if(($#<2))
then
echo"Pleaseenterinstancenameasthesecondparameter!"
exit0
fi
sqlplus-s<system/$1@$2
setheadingoff
setfeedoff
setpagesize200
setlinesize100
spoolstop_index_monitoring.sql
select'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'NOMONITORINGUSAGE;'
fromdba_indexes
whereownernotin('SYS','SYSTEM','OUTLN','AURORA/$JIS/$UTILITY/$');
spooloff
exit
!
exit
sqlplus-s<oracle/$1@$2
@./stop_index_monitoring.sql
exit
!
　　这个脚本将会为所有未被使用的索引产生一个报表：

#####################################################################
##identify_unused_index.sh## 财,管家园,fs119.net
#####################################################################
#!/bin/ksh
#inputparameter:1:password
#2:SID
if(($#<1))
then
echo"Pleaseenter'system'userpasswordasthefirstparameter!"
exit0
fi
if(($#<2))
then
echo"Pleaseenterinstancenameasthesecondparameter!"
exit0
fi
sqlplus-s<system/$1@$2
setfeedoff
setpagesize200
setlinesize100
ttitlecenter"UnusedIndexesReport"skip2
spoolunused_index.rpt
selectowner,index_name,table_name,used
fromv\$all_object_usage
whereused='NO';
spooloff
exit
!
　　以下就是一个未被使用索引报表的例子：

UnusedIndexesReport

OWNERINDEX_NAMETABLE_NAMEUSE
--------------------------------------------------------------------------------HRDEPT_ID_PKDEPARTMENTSNO
HRDEPT_LOCATION_IXDEPARTMENTSNO
HREMP_DEPARTMENT_IXEMPLOYEESNO
HREMP_EMAIL_UKEMPLOYEESNO
HREMP_EMP_ID_PKEMPLOYEESNO 财.软联盟.fs119.net
HREMP_JOB_IXEMPLOYEESNO
HREMP_MANAGER_IXEMPLOYEESNO
HREMP_NAME_IXEMPLOYEESNO
HRJHIST_DEPARTMENT_IXJOB_HISTORYNO
HRJHIST_EMPLOYEE_IXJOB_HISTORYNO
HRJHIST_EMP_ID_ST_DATE_PKJOB_HISTORYNO
HRJHIST_JOB_IXJOB_HISTORYNO
HRJOB_ID_PKJOBSNO
HRLOC_CITY_IXLOCATIONSNO
HRLOC_COUNTRY_IXLOCATIONSNO
HRLOC_ID_PKLOCATIONSNO
HRLOC_STATE_PROVINCE_IXLOCATIONSNO
HRREG_ID_PKREGIONSNO
OEINVENTORY_PKINVENTORIESNO
OEINV_PRODUCT_IXINVENTORIESNO
OEINV_WAREHOUSE_IXINVENTORIESNO
OEITEM_ORDER_IXORDER_ITEMSNO
OEITEM_PRODUCT_IXORDER_ITEMSNO
OEORDER_ITEMS_PKORDER_ITEMSNO
OEORDER_ITEMS_UKORDER_ITEMSNO
OEORDER_PKORDERSNO
　　结论

　　Oracle9i为监控索引的使用提供了一个新的方法，并且帮助我们辨别未被使用的索引。这个查找和删除未被使用索引的能力不但对插入和删除操作的性能有帮助，而且还节省了存储空间。在使用索引监控的时候不会看到性能的下降。 财管家园,fs119.net

财软 联盟 fs119.net