OCP-043 monitor index use

56. 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

MONITORING USAGE | NOMONITORING USAGE

Use this clause to determine whether Oracle Database should monitor index use.

• Specify MONITORING USAGE to begin monitoring the index. Oracle Database first clears existing information on index use, and then monitors the index for use until a subsequent ALTER INDEX ... NOMONITORING USAGE statement is executed.

• To terminate monitoring of the index, specify NOMONITORING USAGE.

To see whether the index has been used since this ALTER INDEX ... NOMONITORING USAGE statement was issued, query the USED column of the V$OBJECT_USAGEdynamic performance view.

V$OBJECT_USAGE

You can use this view to monitor index usage. The view displays statistics about index usage gathered from the database. All indexes that have been used at least once can be monitored and displayed in this view.

Column	Datatype	Description
INDEX_NAME	VARCHAR2(30)	Index name in sys.obj$.name
TABLE_NAME	VARCHAR2(30)	Table name in sys.obj$.name
MONITORING	VARCHAR2(3)	YES| NO
USED	VARCHAR2(3)	YES| NO
START_MONITORING	VARCHAR2(19)	Start monitoring time in sys.object_stats.start_monitoring
END_MONITORING	VARCHAR2(19)	End monitoring time in sys.object_stats.end_monitoring