Oracle9i中监视索引的使用

最新推荐文章于 2021-04-06 01:23:44 发布

cuipeng3714

最新推荐文章于 2021-04-06 01:23:44 发布

阅读量82

点赞数

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

辨别未使用的索引

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

ALTER INDEX index_name MONITORING USAGE;

要停止监控一个索引，输入：

ALTER INDEX index_name NOMONITORING USAGE;

在v$objec_usage视图中包含有索引监控的使用信息。[@more@]

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

介绍

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

结论

Oracle9i为监控索引的使用提供了一个新的方法，并且帮助我们辨别未被使用的索引。这个查找和删除未被使用索引的能力不但对插入和删除操作的性能有帮助，而且还节省了存储空间。在使用索引监控的时候不会看到性能的下降。

对于DML操作来说，索引对于数据库是一个性能负担.如果索引没有被有效的使用,那么其存在性就值得从新考虑.
1. 从Oracle9i开始,Oracle允许你监视索引的使用:

SQL> connect scott/tiger@conner
Connected to Oracle9i Enterprise Edition Release 9.2.0.4.0 
Connected as scott

SQL> select index_name from user_indexes;

INDEX_NAME
------------------------------
PK_DEPT
PK_EMP



SQL> alter index pk_dept monitoring usage;

Index altered



SQL> select * from dept where deptno=10;

DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK



SQL> alter index pk_dept nomonitoring usage;

Index altered



SQL> select * from v$object_usage;

INDEX_NAME        TABLE_NAME         MONITORING USED START_MONITORING    END_MONITORING
----------------- ------------------ ---------- ---- ------------------- -------------------
PK_DEPT           DEPT               NO         YES  10/28/2004 10:55:19 10/28/2004 10:55:47

SQL>

2.Oracle9i的Bug

在9205之前，如果你不慎监控了SYS.I_OBJAUTH1索引，并且不幸在重起数据库之前没有停止它，那么你的数据库将会无法启动，并且
不会给出任何错误信息。

以下这条简单的语句可以轻易再现这个问题:

"ALTER INDEX SYS.I_OBJAUTH1 MONITORING USAGE"

如果你有了足够好的备份(严重警告，请不要拿你的生产数据库进行测试)，你可以尝试一下:

[oracle@jumper oradata]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Dec 4 10:09:30 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL> alter index SYS.I_OBJAUTH1 monitoring usage ;

Index altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 80811208 bytes
Fixed Size 451784 bytes
Variable Size 37748736 bytes
Database Buffers 41943040 bytes
Redo Buffers 667648 bytes
Database mounted.

此时，数据库挂起，而且不会有任何提示，在alert.log文件中，你可以看到:

然后数据库将会停在此处。

如果不知道此bug存在，你可能会一筹莫展的。

现在你能做的就是从备份中恢复，或者升级到9.2.0.5。

Oracle已经Release了这个Bug，你可以参考Metalink:Note:2934068.8,Oracle声明在9.2.0.5 (Server Patch Set)和 10g Production Base Release中fixed了这个Bug。

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Dec 4 10:19:07 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 80811208 bytes
Fixed Size 451784 bytes
Variable Size 37748736 bytes
Database Buffers 41943040 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>

3. 在特殊的情况下,你可能需要清除这个v$object_usage视图中的信息.

Oracle的说法是,在下一次收集该对象的索引使用情况时会自动覆盖上一次的信息,不提供清除手段.

稍微研究了一下.

v$object_usage是基于以下基表建立起来的:


create or replace view 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#
/

注意到v$object_usage关键信息来源于OBJECT_USAGE表.
另外我们可以注意一下,此处v$object_usage的查询基于userenv("SCHEMAID")建立.
所以以不同用户登录,你是无法看到其他用户的索引监视信息的,即使是dba,但是可以从object_usage表中得到.

SQL> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
PK_DEPT                        DEPT                           NO  YES 10/28/2004 10:55:19 10/28/2004 10:55:47

SQL> select * from object_usage;
select * from object_usage
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> connect /as sysdba
Connected.
SQL> /

      OBJ#      FLAGS START_MONITORING    END_MONITORING
---------- ---------- ------------------- -------------------
      6288          1 10/28/2004 10:55:19 10/28/2004 10:55:47

实际上我们清除了object_usage表的记录,实际上也就清空了v$object_usage的信息.

SQL> delete from object_usage;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from v$object_usage;

no rows selected

此操作对数据库没有潜在的影响,但是请谨慎使用.作为实验目的提供.

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

转载于:http://blog.itpub.net/330796/viewspace-895422/

cuipeng3714

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫