189: You have many users complaining about slow inserts into a large table.

While investigating the reason,you find that the number of indexes on the

table is high. You want to find out which indexes are not being used.

Which method would you follow to achieve this?

A.enable index monitoring and query the DBA_OBJECTS view

B.enable index monitoring and query the DBA_INDEXES view

C.enable index monitoring and query the V$OBJECT_USAGE view

D.enable index monitoring and view the DBA_INDEXTYPE_COMMENTS view

Answer: C

1: $object_usage可以用来监控系统中索引的使用情况

2: 监控索引的使用：SQL> alter index index_test_pk monitoring usage;

3: SQL> select * from v$object_usage;

4: $object_usage只包括当前用户的索引使用记录，如果需要查出所有用户的索

引使用记录，使用下面的sql，这个sql来自DBA日记：

SQL> select u.name owner, io.name index_name, t.name table_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#;

5: 取消对索引的监控

SQL> alter index index_test_pk nomonitoring usage;

ITPub博客

043-189