Oracle9i中监视索引的使用(1)

介绍

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视图中删除。
介绍

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视图中删除。