监视索引的使用in Oracle9i

监视索引的使用in Oracle9i
henrybai
简介：
DBA和开发者都衷爱索引. 因为索引能加快查询的速度，特别是在数据仓库的环境，为了避免全表扫描。我们为每个可能被查询的列加上索引。但索引有时也会为更新和插入增加许多额外的开 销。 特别在ORACLE9I中。很难发现哪些索引是有用的，哪些是没有用的。本文介绍了怎样判断哪些索引被使用了。哪些没有被使用。
找出没用被使用的索引。
Oracle9i 中提供了一些机制来监视哪些索引被使用了，哪些没有被使用。使用下列命令开始监视一个索引的使用情况。
ALTER INDEX index_name MONITORING USAGE;
停止监视命令。
ALTER INDEX index_name NOMONITORING USAGE;
监视到索引的使用信息被存储在 V$OBJECT_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登录。执行下列脚本。
$ 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
------------------------------ ------------------------------ ------------------------------ ---
WMSYS MODIFIED_TABLES_PK WM$MODIFIED_TABLES NO
WMSYS SYS_C001454 WM$VERSIONED_TABLES NO
WMSYS SYS_C001463 WM$REPLICATION_TABLE NO
WMSYS WM$ADT_FUNC_TAB_TNAME WM$ADT_FUNC_TABLE NO
WMSYS WM$ENV_VARS_PK WM$ENV_VARS NO
WMSYS WM$INSTEADOF_TRIGS_PK WM$INSTEADOF_TRIGS_TABLE NO
WMSYS WM$LOCKROWS_INFO_IDX WM$LOCKROWS_INFO NO
WMSYS WM$MOD_TAB_VER_IND WM$MODIFIED_TABLES NO
WMSYS WM$NESTED_COLUMNS_PK WM$NESTED_COLUMNS_TABLE NO
WMSYS WM$NEXTVER_TABLE_INDX WM$NEXTVER_TABLE NO
WMSYS WM$NEXTVER_TABLE_NV_INDX WM$NEXTVER_TABLE NO
WMSYS WM$RESOLVE_WORKSPACES_PK WM$RESOLVE_WORKSPACES_TABLE NO
WMSYS WM$RIC_PK WM$RIC_TABLE NO
WMSYS WM$RIC_TABLE_CT_IDX WM$RIC_TABLE NO
WMSYS WM$RIC_TABLE_PT_IDX WM$RIC_TABLE NO
WMSYS WM$RIC_TRIGGERS_PK WM$RIC_TRIGGERS_TABLE NO
WMSYS WM$TMP_DBA_CONS_IND WM$TMP_DBA_CONSTRAINTS NO
WMSYS WM$UDTRIG_DISPATCH_PROCS_PK WM$UDTRIG_DISPATCH_PROCS NO
WMSYS WM$UDTRIG_INFO_INDX WM$UDTRIG_INFO NO
WMSYS WM$UDTRIG_INFO_PK WM$UDTRIG_INFO NO
WMSYS WM$VERSIONED_TABLES__PK WM$VERSIONED_TABLES NO
WMSYS WM$VERSION_HIERARCHY_PK WM$VERSION_HIERARCHY_TABLE NO
WMSYS WM$VERSION_PK WM$VERSION_TABLE NO
WMSYS WM$VHT_IDX WM$VERSION_HIERARCHY_TABLE NO
WMSYS WM$VT_ERRORS_PK WM$VT_ERRORS_TABLE NO
WMSYS WM$WORKSPACES_PK WM$WORKSPACES_TABLE NO
WMSYS WM$WORKSPACE_SAVEPOINTS_PK WM$WORKSPACE_SAVEPOINTS_TABLENO
WMSYS WM$WS_PRIV_TAB_GRTE_IND WM$WORKSPACE_PRIV_TABLE NO
WMSYS WM$WS_PRIV_TAB_GRTOR_IND WM$WORKSPACE_PRIV_TABLE NO
WMSYS WM$WS_PRIV_TAB_WS_GRTE_IND WM$WORKSPACE_PRIV_TABLE NO
WMSYS WM$WS_SP_TAB_VER_IND WM$WORKSPACE_SAVEPOINTS_TABLENO
WMSYS WM$WS_TAB_PVER_IND WM$WORKSPACES_TABLE NO
WMSYS WM$WS_TAB_PWS_IND WM$WORKSPACES_TABLE NO
localhost.localdomain:/oracle9>
结论
Oracle9i 提供了哪些索引是有效的，哪些是无效索引的功能。利用这一功能能够删除哪些没有被使用的索引。这样不但可以提高空间的使用效率，而且也可以提高插入和删除的性能。