ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 9i中监视索引的使用

Oracle 9i中监视索引的使用

原创 Linux操作系统 作者:tolywang 时间:2007-06-21 00:00:00 0 删除 编辑

介绍

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


辨别未使用的索引

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

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"
/

该视图显示了由数据库中收集来的索引使用统计。以下就是该视图中的列的描述:

 INDEX_NAME: sys.obj$.name 中的索引名字

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

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

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

 START_MONITORING: 开始监控的时间 

 END_MONITORING: 结束监控的时间

所有被使用过至少一次的索引都可以被监控并显示到这个视图中。不过,一个用户只可以接收它自己模式中的索引使用。Oracle并没有提供一个视图来接收所有模式中的索引。要接收所有模式的索引使用,以SYS用户登录并且运行以下的脚本(注意:这并不是Oracle提供的一个脚本。v$all_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 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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-84680/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13203573