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" /
该视图显示了由数据库中收集来的索引使用统计。以下就是该视图中的列的描述：
$ 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
结论
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
