Database Reference
In-Depth Information
CHAPTER 4
■ ■ ■
IND$, V$OBJECT_USAGE,
and Index Monitoring
T he view V$OBJECT_USAGE is partially documented in the Oracle Database Reference manual
and in the Oracle Database Administrator's Guide . The purpose of this view is to classify indexes as
used or unused. Since SELECT statements don't benefit from unused indexes, and modifica-
tions through INSERT, UPDATE, and DELETE statements must maintain indexes, it may be
worthwhile to drop unused indexes.
It is undocumented that the view V$OBJECT_USAGE can only be queried for information
on indexes within a single schema at a time while logged in as the user corresponding to the
schema. Furthermore, it is undocumented that ALTER INDEX REBUILD switches off index moni-
toring and marks the rebuilt index as used. Last but not least, it is undocumented that there is
a performance penalty for index monitoring, since it causes the execution of recursive SQL
statements each time a monitored index is used. The Oracle Database SQL Reference manual
incorrectly states that the MONITORING USAGE clause of ALTER INDEX may only be used on indexes
owned by the user who executes ALTER INDEX . Additional undocumented aspects are that index
usage monitoring cannot be used for primary key indexes of index-organized tables (the error
“ORA-25176: storage specification not permitted for primary key” would be raised) and domain
indexes (“ORA-29871: invalid alter option for a domain index” would result).
This chapter presents an improved view for index usage information that is built directly
on data dictionary base tables. The enhanced view removes the restriction of merely retrieving
information on indexes owned by the current user. It takes the effects of ALTER INDEX REBUILD
into account and designates only those indexes as used, which were accessed by DML, and not
an index rebuild operation. The enhanced view allows a DBA to detect superfluous indexes in
all schemas of a database.
Schema Restriction
V$OBJECT_USAGE is a misnomer for a view that is based only on data dictionary tables in schema
SYS and not on X$ fixed tables. The prefix V$ suggests that it is a dynamic performance view,
but it is not. The lack of a column called “OWNER” might send you wondering how the DBA is
supposed to find out which indexes in an application schema have been used. After all, views
such as DBA_INDEXES and DBA_SEGMENTS have a column “OWNER”, and dynamic performance
views such as V$ACCESS and V$SEGMENT_STATISTICS also have a column called “OWNER”, such
that the DBA can view information for any schema he or she chooses. If you thought you as the
45
 
Search WWH ::




Custom Search