数据库

位置:IT落伍者 >> 数据库 >> 浏览文章

Oracle9i中监视索引的使用


发布日期:2022年09月27日
 
Oracle9i中监视索引的使用

介绍

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

辨别未使用的索引

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

ALTER INDEX index_name MONITORING USAGE;

要停止监控一个索引输入

ALTER INDEX index_name NOMONITORING USAGE;

在v$objec_usage视图中包含有索引监控的使用信息

CREATE OR REPLACE VIEW SYSV$OBJECT_USAGE

(

INDEX_NAME

TABLE_NAME

MONITORING

USED

START_MONITORING

END_MONITORING

)

AS

select ioname tname

decode(bitand(iflags ) NO YES)

decode(bitand(ouflags ) NO YES)

oustart_monitoring

ouend_monitoring

from sysobj$ io sysobj$ t sysind$ i sysobject_usage ou

where ioowner# = userenv(SCHEMAID)

and iobj# = ouobj#

and ioobj# = ouobj#

and tobj# = ibo#

/

COMMENT ON TABLE SYSV$OBJECT_USAGE IS

Record of index usage

/

GRANT SELECT ON SYSV$OBJECT_USAGE TO PUBLIC

/

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

INDEX_NAME: sysobj$name 中的索引名字

TABLE_NAME: sysobj$obj$name 中的表名

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

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

START_MONITORING: 开始监控的时间

END_MONITORING: 结束监控的时间

所有被使用过至少一次的索引都可以被监控并显示到这个视图中不过一个用户只可以接收它自己模式中的索引使用Oracle并没有提供一个视图来接收所有模式中的索引要接收所有模式的索引使用以SYS用户登录并且运行以下的脚本(注意这并不是Oracle提供的一个脚本v$all_object_usage是一个自定义的视图它包含多一个列即索引的拥有者)

$ cat all_object_usagesql

CREATE OR REPLACE VIEW SYSV$ALL_OBJECT_USAGE

(

OWNER

INDEX_NAME

TABLE_NAME

MONITORING

USED

START_MONITORING

END_MONITORING

)

AS

select uname ioname tname

decode(bitand(iflags ) NO YES)

decode(bitand(ouflags ) NO YES)

oustart_monitoring

ouend_monitoring

from sysobj$ io sysobj$ t sysind$ i sysobject_usage ou sysuser$ u

where iobj# = ouobj#

and ioobj# = ouobj#

and tobj# = ibo#

and ioowner# = uuser#

/

COMMENT ON TABLE SYSV$ALL_OBJECT_USAGE IS

Record of all index usage developed by Daniel Liu

/

GRANT SELECT ON SYSV$ALL_OBJECT_USAGE TO PUBLIC

/

CREATE PUBLIC SYNONYM V$ALL_OBJECT_USAGE

FOR SYSV$ALL_OBJECT_USAGE

/

每次你使用MONITORING USAGE视图就会为特别的索引而复位所有以前的使用信息都会被清除和复位并且会记录下一个新的启动时间每次你执行NOMONITORING USAGE就不会进行进一步的监控监视期间的结束时间就会被记录下来如果你删除一个正在被监控的索引该索引的相关信息就会由V$OBJECT_USAGE和V$ALL_OBJECT_USAGE视图中删除

辨别数据库中所有未被使用的索引

这个脚本将会启动监控所有的索引

##################################################################### ## start_index_monitoringsh ##

#####################################################################

#!/bin/ksh

# input parameter: : password

# : 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

上一篇:ORACLE中的两个概念:user和schema的区别和联系

下一篇:Oracle 数据表分区的策略