Database Reference
In-Depth Information
What we're seeing now is that as expected
MONITORING=NO
for all indexes. Note the subtle
difference between the index
DEPT_ID_PK
, which had index monitoring switched off due to an
ALTER
INDEX
REBUILD
, and the index
EMP_DEPARTMENT_IX
, which had index monitoring switched
off with
ALTER
INDEX
index_name
NOMONITORING
by the function
MONITOR_SCHEMA_INDEXES
. The
former has
END_MONITORING
set to
NULL
, whereas the latter has the point in time when index
monitoring was switched off. This is a clue for distinguishing between an index rebuild and a
genuine index usage due to DML.
Taking all of the findings into account, the following cases have to be considered:
Rebuilt indexes are marked as used and monitoring on them is switched off, while leaving
the value
END_MONITORING
set to
NULL
. Since we are only interested in index usage due to
DML, we need to exclude this case.
Indexes that were used by DML retain the settings of
MONITORING
(
YES
) and
END_MONITORING
(
NULL
).
Indexes on which monitoring was switched off after they were used by DML retain the
setting
MONITORING=YES
, but have an actual timestamp instead of
NULL
in
END_MONITORING
.
The following query retrieves only indexes that were marked as used by DML, but not by
an index rebuild:
SQL> SELECT * FROM v$object_usage
WHERE (monitoring='YES' AND used='YES') OR
(used='YES' AND end_monitoring IS NOT NULL)
ORDER BY index_name;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
----------------- ---------- ---------- ---- ------------------- -------------------
EMP_DEPARTMENT_IX EMPLOYEES NO YES 10/04/2007 17:21:55 10/04/2007 18:17:58
This essentially solves the issue of index monitoring, apart from the annoyance that a DBA
cannot retrieve information on foreign schemas, except by connecting with the user name that
is identical to the schema name. This is the time for data dictionary base tables to make their
appearance on stage. After dwelling on the definition of
V$OBJECT_USAGE
in the file
catalog.sql
for a moment, it is not hard to write an enhanced version of the view, which deserves the name
DBA_INDEX_USAGE
, i.e., a view that allows access to index usage information for all indexes in a
database, not just within the current schema. Since I don't intend to cause confusion by imitating
Oracle Corporation's naming convention, I will simply call the view
INDEX_USAGE
. The script
view_index_usage.sql
to create it is reproduced below. It adds the column
OWNER
, which it retrieves
from
SYS.USER$
.
USER$
, has to be joined with
OBJ$
using
OBJ$.OWNER#=USER$.USER#
to retrieve
the names of index owners. I'm creating database objects in schema
SITE_SYS
to prevent inter-
ference with the data dictionary in schema
SYS
.
