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