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

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