监视索引的使用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_TABLE  NO
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_TABLE  NO
WMSYS                          WM$WS_TAB_PVER_IND             WM$WORKSPACES_TABLE            NO
WMSYS                          WM$WS_TAB_PWS_IND              WM$WORKSPACES_TABLE            NO
localhost.localdomain:/oracle9>
结论
Oracle9i 提供了哪些索引是有效的，哪些是无效索引的功能。利用这一功能能够删除哪些没有被使用的索引。这样不但可以提高空间的使用效率，而且也可以提高插入和删除的性能。