索引监控(转载)

简介：
DBA和开发者都衷爱索引. 因为索引能加快查询的速度，特别是在数据仓库的环境，为了避免全表扫描。我们为每个可能被查询的列加上索引。但索引有时也会为更新和插入增加许多额外的开销。 特别在ORACLE9I中。很难发现哪些索引是有用的，哪些是没有用的。本文介绍了怎样判断哪些索引被使用了。哪些没有被使用。
找出没用被使用的索引。
Oracle9i 中提供了一些机制来监视哪些索引被使用了，哪些没有被使用。

 

使用下列命令开始监视一个索引的使用情况。
ALTER INDEX index_name MONITORING USAGE;
停止监视命令。
ALTER INDEX index_name NOMONITORING USAGE;

监视到索引的使用信息被存储在 V$OBJECT_USAGE 视图中。

视图中的一些列的具体含义：
INDEX_NAME: sys.obj$.name中索引的名字。
TABLE_NAME: sys.obj$obj$name中表的名字。
MONITORING: YES (索引正在被监视), NO (索引没有被监视)
USED: YES（索引被使用了） NO (索引没有被使用)
START_MONITORING: 监视开始的时间。
END_MONITORING: 监视结束的时间。
所有被使用过的索引，哪怕被用过一次也会显示在这个视图中。但一个用户只能监视他自己模式中索引的使用情况。ORACLE没有提供监视所有模式中的索引的功能。要想监视所有模式中的索引的使用情况，请用SYS登录。执行下列脚本。

创建监视到索引V$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 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
!
下面是一个报告的例子：

未使用索引情况

OWNER	INDEX_NAME	TABLE_NAME	MONITORING	USED
DBSNMP	MGMT_DB_FILE_GTT_PK	MGMT_DB_FILE_GTT	YES	NO
DBSNMP	MGMT_DB_SIZE_GTT_PK	MGMT_DB_SIZE_GTT	YES	NO
DBSNMP	BSLN_DATASOURCES_PK	MGMT_BSLN_DATASOURCES	YES	NO
DBSNMP	BSLN_DATASOURCES_UK1	MGMT_BSLN_DATASOURCES	YES	NO
DBSNMP	BSLN_BASELINES_PK	MGMT_BSLN_BASELINES	YES	NO
DBSNMP	BSLN_BASELINES_UK1	MGMT_BSLN_BASELINES	YES	NO
DBSNMP	BSLN_METRICS_PK	MGMT_BSLN_METRICS	YES	NO
DBSNMP	BSLN_STATISTICS_PK	MGMT_BSLN_STATISTICS	YES	NO
DBSNMP	BSLN_THRESHOLDS_PK	MGMT_BSLN_THRESHOLD_PARMS	YES	NO

 结论
Oracle9i 提供了哪些索引是有效的，哪些是无效索引的功能。利用这一功能能够删除哪些没有被使用的索引。这样不但可以提高空间的使用效率，而且也可以提高插入和删除的性能。