oracle索引监控-----未使用的索引

oracle索引监控-----未使用的索引

oracle索引是用来优化DML处理速度，但是索引是会占用表空间，有时会占用比被索引表占用还要多的表空间。所以，索引监控未使用索引就显得很重要。

打开某个索引监控：

alter index owner.index_name monitoring usage;

关闭某个索引监控：

alter index owner.index_name nomonitoring usage;

在v$object_usage视图中包含有索引监控的使用信息。下面v$object_usage在oracle中的ddl语句：

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

这个试图只能查当前登录数据库的用户所创建的索引。

所以我们可以依葫芦画瓢，自己写sql语句。不过你必须要有相应的权限才行，具体权限自己加就行了。

只要是这几个表的查询权限(sys.obj$, sys.obj$, sys.ind$, sys.object_usage)。

sql略去。。。

alter index INDEX_USER monitoring usage;

select table_name,index_name,used from v$object_usage; 

alter index pk_emp nomonitoring usage;

select * from v$object_usage;

只是当前用户下有效，只能看到当前用户下的监视索引。

SQL> COL INDEX_NAME FOR A20       

SQL> COL TABLE_NAME FOR A10

SQL> COL MONITORING FOR A10

SQL> COL USED FOR A10

SQL> COL START_MONITORING FOR A20

SQL> COL END_MONITORING FOR A20

SQL> SELECT * FROM V$OBJECT_USAGE;

select z.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$ z

Where  i.obj# = ou.obj#

and io.obj# = ou.obj#  and io.owner#=z.user#

and t.obj# = i.bo#  

Order By 4 Desc,2 Desc; 

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';

GRANT SELECT ON SYS.V$ALL_OBJECT_USAGE TO "PUBLIC";

CREATE PUBLIC SYNONYM V$ALL_OBJECT_USAGE FOR SYS.V$ALL_OBJECT_USAGE;

V$OBJECT_USAGE

You can use this view to monitor index usage. The view displaysstatistics about index usage gathered from the database. Allindexes that have been used at least once can be monitored anddisplayed in this view.

注意：

1、$object_usage只包括当前用户所拥有索引的使用记录，即索引的创建者或者是索引的拥有者(owner),已这个用户登录后，看到的是此用户下拥有的索引在整个数据库的使用情况。如果使用了该索引，视图V$OBJECT_USAGE的字段USED会标成YES。否则是NO。当然首先是这些索引被启用使用监视。SQL>alter index index_test_pk monitoring usage;

2、如果需要查出所有用户所拥有索引的使用记录，使用下面的sql，这个sql来自DBA日记：

SQL> select u.name owner, io.name index_name, t.nametable_name,

decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring,

decode(bitand(ou.flags, 1), 0, 'NO', 'YES')used,ou.start_monitoring

start_monitoring,ou.end_monitoring end_monitoring

from sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i,sys.object_usage

ou where i.obj# = ou.obj# and io.obj# = ou.obj# and t.obj# =i.bo#

and u.user# = io.owner#;

---------------------